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

Use the formats in INSERT and UPDATE statements.

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 datetimeformat option in a cqlshrc file to change the COPY TO date parsing format.

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) and 2mo10d12h (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 are Y (years), M (months), and D (days). T is a time designator separating the years, months, and days from the time units: H (hours), M (minutes), and S (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 the W (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 as 0004 for four years.

  • MM: A number of months represented as two digits, such as 06 for six months.

  • DD: A number of days represented as two digits, such as 03 for three days.

  • hh: A number of hours represented as two digits, such as 12 for twelve hours.

  • mm: A number of minutes represented as two digits, such as 30 for thirty minutes.

  • ss: A number of seconds represented as two digits, such as 05 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

timestamp combines date and time with the addition of timezone in ISO 8601 format:

<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

Was this helpful?

Give Feedback

How can we improve the documentation?

© 2025 DataStax, an IBM Company | Privacy policy | Terms of use | Manage Privacy Choices

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