Timestamp column
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 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
+0000is GMT and-0800is PST.
-
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).
Using the current date/time:
Date with time set to midnight using UTC:
-
CQL INSERT
-
CQL SELECT
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
WARNING: cqlsh was built against 5.0-beta1, but this server is 5.0. All features may not work!
created_at
---------------------------------
2024-06-21 00:00:00.000000+0000
(1 rows)Full timestamp:
-
CQL INSERT
-
CQL SELECT
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
WARNING: cqlsh was built against 5.0-beta1, but this server is 5.0. All features may not work!
created_at
---------------------------------
2024-06-21 01:05:29.257000+0000
(1 rows)Using string format:
Date with time and no timezone sets the timezone to UTC:
-
CQL INSERT
-
CQL SELECT
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
WARNING: cqlsh was built against 5.0-beta1, but this server is 5.0. All features may not work!
created_at
---------------------------------
2017-04-01 00:00:00.000000+0000
(1 rows)Date with time and timezone sets the timezone to UTC:
-
CQL INSERT
-
CQL SELECT
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
WARNING: cqlsh was built against 5.0-beta1, but this server is 5.0. All features may not work!
created_at
---------------------------------
2017-04-01 00:00:00.000000+0000
(1 rows)Full timestamp using UTC:
-
CQL INSERT
-
CQL SELECT
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
WARNING: cqlsh was built against 5.0-beta1, but this server is 5.0. All features may not work!
created_at
------------
(0 rows)