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.

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)

Was this helpful?

Give Feedback

How can we improve the documentation?

© 2024 DataStax | Privacy policy | Terms of use

Apache, Apache Cassandra, Cassandra, Apache Tomcat, Tomcat, Apache Lucene, Apache Solr, Apache Hadoop, Hadoop, Apache Pulsar, Pulsar, Apache Spark, Spark, Apache TinkerPop, TinkerPop, Apache Kafka and Kafka are either registered trademarks or trademarks of the Apache Software Foundation or its subsidiaries in Canada, the United States and/or other countries. Kubernetes is the registered trademark of the Linux Foundation.

General Inquiries: +1 (650) 389-6000, info@datastax.com