Timestamp column

Upsert the current or a specific date/time into a timestamp field using string format.

Prerequisites

A timestamp combines both date and time with the addition of time zone in ISO 8601 format:

``yyyy-mm-dd`[(T| )`HH:MM:SS[.fff]`][`(+|-)<NNNN>`]`

Where only the date portion of the timestamp is required:

(Required) date (yyyy-mm-dd), where
- yyyy: four-digit year
- mm: two-digit month
- dd: two-digit day
time (HH:MM:SS[.fff], where
- HH: two-digit hour using a 24 hour clock.
- MM: two-digit minute.
- SS: two-digit seconds.
- (Optional) .fff: up to three-digit subseconds; when excluded it’s set to zero (0).
  
  When time is excluded, it’s set to zero. When timezone is excluded, it’s set to the client or coordinator timezone.
timezone ((+|-)NNNN): is the offset from GMT.
- indicates whether to add or subtract the NNNN from GMT
- NNNN is the RFC-822 4-digit time zone, for example +0000 is GMT and -0800 is PST.

Use functions to insert the current date into date or timestamp fields as follows:

Current date and time into timestamp field: toTimestamp(now()) sets the timestamp to the current time of the coordinator.
Current date (midnight) into timestamp field: toTimestamp(toDate(now())) sets the timestamp to the current date beginning of day (midnight).

Date with time set to midnight using UTC:

INSERT INTO cycling.comments (id, created_at) 
  VALUES (e7ae5cf3-d358-4d99-b900-85902fda9bb0,toTimeStamp(toDate(now())));

SELECT created_at FROM cycling.comments 
  WHERE id = e7ae5cf3-d358-4d99-b900-85902fda9bb0
 LIMIT 1;

Results

 created_at
---------------------------------
 2024-07-02 00:00:00.000000+0000

(1 rows)

Full timestamp:

INSERT INTO cycling.comments (id, created_at) 
  VALUES (e7ae5cf3-d358-4d99-b900-85902fda9bb0,toTimeStamp(now()));

SELECT created_at FROM cycling.comments 
  WHERE id = e7ae5cf3-d358-4d99-b900-85902fda9bb0
  LIMIT 1;

Results

 created_at
---------------------------------
 2024-07-02 22:39:04.427000+0000

(1 rows)

Date with time and no timezone sets the timezone to UTC:

INSERT INTO cycling.comments (id, created_at) 
  VALUES (e7ae5cf3-d358-4d99-b900-85902fda9bb0, '2017-04-01');

SELECT created_at FROM cycling.comments 
  WHERE id = e7ae5cf3-d358-4d99-b900-85902fda9bb0
  AND created_at = '2017-04-01';

Results

 created_at
---------------------------------
 2017-04-01 00:00:00.000000+0000

(1 rows)

Date with time and timezone sets the timezone to UTC:

INSERT INTO cycling.comments (id, created_at) 
  VALUES (e7ae5cf3-d358-4d99-b900-85902fda9bb0, '2017-04-01+0000');

SELECT created_at FROM cycling.comments 
  WHERE id = e7ae5cf3-d358-4d99-b900-85902fda9bb0
  AND created_at = '2017-04-01+0000';

Results

 created_at
---------------------------------
 2017-04-01 00:00:00.000000+0000

(1 rows)

Full timestamp using UTC:

INSERT INTO cycling.comments (id, created_at) 
  VALUES (e7ae5cf3-d358-4d99-b900-85902fda9bb0, '2017-04-01T11:21:59.001+0000');

SELECT created_at FROM cycling.comments 
  WHERE id = e7ae5cf3-d358-4d99-b900-85902fda9bb0
  AND created_at = '2017-04-01T11:21:59.001-0800';

Results

 created_at
------------

(0 rows)