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
+0000
is GMT and-0800
is PST.
-
Commands in this section, use the Setting up the Cycling keyspace and comments table and data. |
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
-
Result
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;
created_at
---------------------------------
2017-06-01 00:00:00.000000+0000
(1 rows)
END-rnow_date */
Full timestamp:
-
CQL INSERT
-
CQL SELECT
-
Result
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;
created_at
---------------------------------
2017-06-01 03:28:41.526000+0000
(1 rows)
Using string format:
Date with time and no timezone sets the timezone to UTC:
-
CQL INSERT
-
CQL SELECT
-
Result
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';
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
-
Result
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';
created_at
---------------------------------
2017-04-01 00:00:00.000000+0000
(1 rows)
Full timestamp using UTC:
-
CQL INSERT
-
CQL SELECT
-
Result
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';
created_at
---------------------------------
2017-04-01 11:21:59.001000-0800
(1 rows)