Timestamp column
Upsert the current or a specific date/time into a timestamp field using string format.
Upsert the current or a specific date/time into a timestamp field using string format.
String format
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 sub-seconds; when excluded it's set to zero (0).
Note: When time is excluded, it's set to zero. - 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.
Note: When timezone is excluded, it's set to the client or coordinator timezone.
Inserting the current timestamp
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).
Procedure
-
Using the current date/time:
- Date with time set to midnight using
UTC.
INSERT INTO cycling.comments ( id, created_at) values ( e7ae5cf3-d358-4d99-b900-85902fda9bb0, toTimeStamp(toDate(now())));
- Full
timestamp
INSERT INTO cycling.comments ( id, created_at) values ( e7ae5cf3-d358-4d99-b900-85902fda9bb0, toTimeStamp(now()));
- Date with time set to midnight using
UTC.
-
Using string format:
- 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');
- Full timestamp using
UTC.
INSERT INTO cycling.comments ( id, created_at) values ( e7ae5cf3-d358-4d99-b900-85902fda9bb0, '2017-04-01T11:21:59.001+0000');
- Date with time and no timezone sets the timezone to
UTC.