Timestamp column

Upsert the current or a specific date/time into a timestamp field using string format.

Prerequisites

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.

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).

Date with time set to midnight using UTC:

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:

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)

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

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:

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:

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)