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.
Tip: Commands in this section, use 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).

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()));
  • 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');