Date, time, and timestamp format
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.
Timestamp also supports upserting the value as an integer. The integer is the number of milliseconds after the Unix epoch (January 1, 1970). |
Date
date string format:
<yyyy>-<mm>-<dd>
The elements of the date are separated with a hyphen:
-
yyyy: four-digit year
-
mm: two-digit month
-
dd: two-digit day
For example, May 5, 2017 would be 2017-05-05
.
When loading data from CSV use the |
Duration
Durations are encoded as three signed integers of variable lengths, where the integers represent the number of months, days, and nanoseconds. The number of months and days are decoded as 32-bit integers. The number of nanoseconds is decoded as a 64-bit integer.
The primary key cannot be the duration
type because it isn’t possible to determine if 1mo
is greater than 29d
without a date context.
The following four formats are accepted for duration values. Don’t use quotes when inserting duration values.
- Duration format
-
<N>y<N>mo<N>w<N>d<N>h<N>s<N>ms<N>us<N>ns
<N>
represents a number of units. The units are as follows:-
y
: years -
mo
: months -
w
: weeks -
d
: days -
h
: hours -
m
: minutes -
s
: seconds -
ms
: milliseconds -
us
orµs
: microseconds -
ns
: nanoseconds
Not all units need to be present. Missing units are set to 0. For example,
12h30m
(12 hours and 30 minutes) and2mo10d12h
(2 months 10 days and 12 hours) are valid. -
- ISO 8601 full format
-
P<N>Y<N>M<N>DT<N>H<N>M<N>S
P
is a period designator placed at the start of the duration value.<N>
represents a number of units. The first three units areY
(years),M
(months), andD
(days).T
is a time designator separating the years, months, and days from the time units:H
(hours),M
(minutes), andS
(seconds).For example,
P4Y6M3DT12H30M5S
represents four years, six months, three days, twelve hours, thirty minutes, and five seconds. - ISO 8601 week format
-
P<N>W
P
is a period designator placed at the start of the duration value.<N>
represents a number of weeks. This format accepts only theW
(weeks) unit.For example,
P6W
is six weeks. - ISO 8601 alternative format
-
This format includes years, months, days, hours, minutes, and seconds with punctuation separators rather than unit designators.
P[YYYY]-[MM]-[DD]T[hh]:[mm]:[ss]
P
is a period designator placed at the start of the duration value.T
is a time designator separating the date from the time. The unit durations are specified as follows:-
YYYY
: A number of years represented as four digits, such as0004
for four years. -
MM
: A number of months represented as two digits, such as06
for six months. -
DD
: A number of days represented as two digits, such as03
for three days. -
hh
: A number of hours represented as two digits, such as12
for twelve hours. -
mm
: A number of minutes represented as two digits, such as30
for thirty minutes. -
ss
: A number of seconds represented as two digits, such as05
for five seconds.Years, months, and days are separated by hyphens (
-
), and hours, minutes, and seconds are separated by colons (:
).For example,
P0004-06-03T12:30:05
is four years, six months, three days, twelve hours, thirty minutes, and five seconds.
-
Time
time string format:
<HH>:<MM>:<SS>[.<fffffffff>]
Where the following values are expected:
-
HH: two-digit hour using a 24 hour clock.
-
MM: two-digit minute.
-
SS: two-digit seconds.
-
(Optional) fffffffff: up to nine-digit nanoseconds, when excluded it’s set to zero (0).
For example, 2:30 PM can be represented in any of the following formats:
14:30:00
14:30:00.000
14:30:00.000000000
Timestamp
<yyyy>-<mm>-<dd>[(T| )<HH>:<MM>:<SS>[.<fff>]][(+|-)<NNNN>]
For example, May 5, 2017 midnight GMT can be represented in any of the following formats:
2017-05-05 00:00:00.000+0000 2017-05-05 00:00:00.000 2017-05-05 00:00:00 2017-05-05
Only the date portion of the timestamp is required. If time is excluded, it’s set to zero. If timezone is excluded, it’s set to the client or coordinator timezone.
In timezone
, the time portion cannot exceed three digits for subseconds (whereas time
alone allows up to nine digits).
The timezone portion expects the format (+|-)<NNNN>, which is the offset from GMT.
`+|-
indicates whether to add or subtract the <NNNN>`from GMT, and `<NNNN>
is the RFC-822 4-digit time zone.
For example +0000
is GMT, and -0800
is PST.
Additional timezone examples
The following table shows additional examples where z
, Z
, and X
represent timezones values.
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 |