Date, time, and timestamp format

Describes string format used to upsert data into columns.

To upsert a value into date, time, or timestamp columns, use the string format or the date or conversion functions (toDate and toTimestamp) with the now function.

Tip: Timestamp also supports upserting the value as an integer. The integer is the number of milliseconds after the Unix epoch (January 1, 1970).
Use the formats in INSERT and UPDATE statements.
date
date string format:
yyyy-mm-dd
Where the elements of a date are separated with a hyphen:
  • yyyy: four-digit year
  • mm: two-digit month
  • dd: two-digit day
For example, May 5, 2017:
2017-05-05
time
time string format:
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).
For example, one o'clock in the afternoon:
13:00:00.000
13:00:00
timestamp
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).
    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.
For example, May 5, 2017 midnight GMT:
2017-05-05 00:00:00.000+0000
2017-05-05 00:00:00.000
2017-05-05 00:00:00
2017-05-05
This table shows additional examples. The z, X, and Z elements represent timezones.
Cassandra 2.2.0 Pattern Example for UTC Timezone Example for Europe/Paris Timezone Example for GMT-07:00 Timezone
yyyy-mm-dd HH:MM 2018-04-26 12:59 2018-04-26 14:59 2018-04-26 05:59
yyyy-mm-dd HH:MM:SS 2018-04-26 12:59:38 2018-04-26 14:59:38 2018-04-26 05:59:38
yyyy-mm-dd HH:MM z 2018-04-26 12:59 UTC 2018-04-26 14:59 CEST 2018-04-26 05:59 GMT-07:00
yyyy-mm-dd HH:MM zz 2018-04-26 12:59 UTC 2018-04-26 14:59 CEST 2018-04-26 05:59 GMT-07:00
yyyy-mm-dd HH:MM zzz 2018-04-26 12:59 UTC 2018-04-26 14:59 CEST 2018-04-26 05:59 GMT-07:00
yyyy-mm-dd HH:MMX 2018-04-26 12:59Z 2018-04-26 14:59+02 2018-04-26 05:59-07
yyyy-mm-dd HH:MMXXX 2018-04-26 12:59Z 2018-04-26 14:59+02:00 2018-04-26 05:59-07:00
yyyy-mm-dd HH:MM:SS 2018-04-26 12:59:38 2018-04-26 14:59:38 2018-04-26 05:59:38
yyyy-mm-dd HH:MM:SS z 2018-04-26 12:59:38 UTC 2018-04-26 14:59:38 CEST 2018-04-26 05:59:38 GMT-07:00
yyyy-mm-dd HH:MM:SS zz 2018-04-26 12:59:38 UTC 2018-04-26 14:59:38 CEST 2018-04-26 05:59:38 GMT-07:00
yyyy-mm-dd HH:MM:SS zzz 2018-04-26 12:59:38 UTC 2018-04-26 14:59:38 CEST 2018-04-26 05:59:38 GMT-07:00
yyyy-mm-dd HH:MM:SSX 2018-04-26 12:59:38Z 2018-04-26 14:59:38+02 2018-04-26 05:59:38-07
yyyy-mm-dd HH:MM:SSXX 2018-04-26 12:59:38Z 2018-04-26 14:59:38+0200 2018-04-26 05:59:38-0700
yyyy-mm-dd HH:MM:SSXXX 2018-04-26 12:59:38Z 2018-04-26 14:59:38+02:00 2018-04-26 05:59:38-07:00
yyyy-mm-dd HH:MM:SS.SSS

(Used to format timestamps as JSON. For example, when the user issues a SELECT JSON query.)
2018-04-26 12:59:38.226 2018-04-26 14:59:38.226 2018-04-26 05:59:38.226
yyyy-mm-dd HH:MM:SS.SSS z 2018-04-26 12:59:38.226 UTC 2018-04-26 14:59:38.226 CEST 2018-04-26 05:59:38.226 GMT-07:00
yyyy-mm-dd HH:MM:SS.SSS zz 2018-04-26 12:59:38.226 UTC 2018-04-26 14:59:38.226 CEST 2018-04-26 05:59:38.226 GMT-07:00
yyyy-mm-dd HH:MM:SS.SSS zzz 2018-04-26 12:59:38.226 UTC 2018-04-26 14:59:38.226 CEST 2018-04-26 05:59:38.226 GMT-07:00
yyyy-mm-dd HH:MM:SS.SSSX 2018-04-26 12:59:38.226Z 2018-04-26 14:59:38.226+02 2018-04-26 05:59:38.226-07
yyyy-mm-dd HH:MM:SS.SSSXX 2018-04-26 12:59:38.226Z 2018-04-26 14:59:38.226+0200 2018-04-26 05:59:38.226-0700
yyyy-mm-dd HH:MM:SS.SSSXXX 2018-04-26 12:59:38.226Z 2018-04-26 14:59:38.226+02:00 2018-04-26 05:59:38.226-07:00
yyyy-mm-dd'T'HH:MM 2018-04-26T12:59 2018-04-26T14:59 2018-04-26T05:59
yyyy-mm-dd'T'HH:MM z 2018-04-26T12:59 UTC 2018-04-26T14:59 CEST 2018-04-26T05:59 GMT-07:00
yyyy-mm-dd'T'HH:MM zz 2018-04-26T12:59 UTC 2018-04-26T14:59 CEST 2018-04-26T05:59 GMT-07:00
yyyy-mm-dd'T'HH:MM zzz 2018-04-26T12:59 UTC 2018-04-26T14:59 CEST 2018-04-26T05:59 GMT-07:00
yyyy-mm-dd'T'HH:MMX 2018-04-26T12:59Z 2018-04-26T14:59+02 2018-04-26T05:59-07
yyyy-mm-dd'T'HH:MMXX 2018-04-26T12:59Z 2018-04-26T14:59+0200 2018-04-26T05:59-0700
yyyy-mm-dd'T'HH:MMXXX 2018-04-26T12:59Z 2018-04-26T14:59+02:00 2018-04-26T05:59-07:00
yyyy-mm-dd'T'HH:MM:SS 2018-04-26T12:59:38 2018-04-26T14:59:38 2018-04-26T05:59:38
yyyy-mm-dd'T'HH:MM:SS z 2018-04-26T12:59:38 UTC 2018-04-26T14:59:38 CEST 2018-04-26T05:59:38 GMT-07:00
yyyy-mm-dd'T'HH:MM:SS zz 2018-04-26T12:59:38 UTC 2018-04-26T14:59:38 CEST 2018-04-26T05:59:38 GMT-07:00
yyyy-mm-dd'T'HH:MM:SS zzz 2018-04-26T12:59:38 UTC 2018-04-26T14:59:38 CEST 2018-04-26T05:59:38 GMT-07:00
yyyy-mm-dd'T'HH:MM:SSX 2018-04-26T12:59:38Z 2018-04-26T14:59:38+02 2018-04-26T05:59:38-07
yyyy-mm-dd'T'HH:MM:SSXX 2018-04-26T12:59:38Z 2018-04-26T14:59:38+0200 2018-04-26T05:59:38-0700
yyyy-mm-dd'T'HH:MM:SSXXX 2018-04-26T12:59:38Z 2018-04-26T14:59:38+02:00 2018-04-26T05:59:38-07:00
yyyy-mm-dd'T'HH:MM:SS.SSS 2018-04-26T12:59:38.226 2018-04-26T14:59:38.226 2018-04-26T05:59:38.226
yyyy-mm-dd'T'HH:MM:SS.SSS z 2018-04-26T12:59:38.226 UTC 2018-04-26T14:59:38.226 CEST 2018-04-26T05:59:38.226 GMT-07:00
yyyy-mm-dd'T'HH:MM:SS.SSS zz 2018-04-26T12:59:38.226 UTC 2018-04-26T14:59:38.226 CEST 2018-04-26T05:59:38.226 GMT-07:00
yyyy-mm-dd'T'HH:MM:SS.SSS zzz 2018-04-26T12:59:38.226 UTC 2018-04-26T14:59:38.226 CEST 2018-04-26T05:59:38.226 GMT-07:00
yyyy-mm-dd'T'HH:MM:SS.SSSX

(Used to format timestamps into CQL literals. Rarely used, and only useful with aggregate functions.)
2018-04-26T12:59:38.226Z 2018-04-26T14:59:38.226+02 2018-04-26T05:59:38.226-07
yyyy-mm-dd'T'HH:MM:SS.SSSXX 2018-04-26T12:59:38.226Z 2018-04-26T14:59:38.226+0200 2018-04-26T05:59:38.226-0700
yyyy-mm-dd'T'HH:MM:SS.SSSXXX 2018-04-26T12:59:38.226Z 2018-04-26T14:59:38.226+02:00 2018-04-26T05:59:38.226-07:00
yyyy-mm-dd 2018-04-26 2018-04-26 2018-04-26
yyyy-mm-dd z 2018-04-26 UTC 2018-04-26 CEST 2018-04-26 GMT-07:00
yyyy-mm-dd zz 2018-04-26 UTC 2018-04-26 CEST 2018-04-26 GMT-07:00
yyyy-mm-dd zzz 2018-04-26 UTC 2018-04-26 CEST 2018-04-26 GMT-07:00
yyyy-mm-ddX 2018-04-26Z 2018-04-26+02 2018-04-26-07
yyyy-mm-ddXX 2018-04-26Z 2018-04-26+0200 2018-04-26-0700
yyyy-mm-ddXXX 2018-04-26Z 2018-04-26+02:00 2018-04-26-07:00