Date and time types

Date and time data types are designed for storing time series data or other timestamps.

When writing date and time values, use single quotes around string formatted values, such as purchase_date = '2017-05-12'. Don’t use quotes for integer values, such as days since epoch format: purchase_date = 17298.

Date

The date type is a 32-bit unsigned integer representing the number of days since epoch (January 1, 1970) with no corresponding time value

Insert or update values as an integer (days since epoch) or in string format '<yyyy>-<mm>-<dd>', for example '2017-05-13'.

When loading data from CSV files use the datetimeformat option to change the COPY TO date parsing format.
Write a date string

Insert a date:

INSERT INTO cycling.cyclist_alt_stats (id,first_race)
  VALUES (ed584e99-80f7-4b13-9a90-9dc5571e6821,'2006-03-15');

Update a date:

UPDATE cycling.cyclist_alt_stats SET birthday = '1987-03-07'
  WHERE id = ed584e99-80f7-4b13-9a90-9dc5571e6821;
Use a date function

Use toDate(now()) to insert the current date into a date field:

INSERT INTO cycling.cyclist_alt_stats (id,last_race)
  VALUES (ed584e99-80f7-4b13-9a90-9dc5571e6821,todate(now()));

You can also use this function to update a date field:

UPDATE cycling.cyclist_alt_stats SET last_race = toDate(now())
  WHERE id = ed584e99-80f7-4b13-9a90-9dc5571e6821;
Retrieve a date value
SELECT first_race, last_race, birthday FROM cycling.cyclist_alt_stats
  WHERE id = ed584e99-80f7-4b13-9a90-9dc5571e6821;
 first_race | last_race  | birthday
------------+------------+------------
 2006-03-15 | 2024-07-02 | 1989-07-05

(1 rows)

DateRangeType

The DateRangeType is similar to the Solr DateRangeField type, it stores a date range in the format [<beginning_date> TO <end_date>]. Insert the custom ranges between square brackets. Truncated timestamps represent the entire date span or use the range syntax to create a custom range. For example:

  • 2018-01: Beginning of the first day to the end of the last day in January 2018.

  • 2018-01T15: Range includes hours of the day from 1500 to before 1600 (3pm to 4pm).

  • [2017-01-15 TO 2017-11-01]: The start of the fifteenth of January through the end of the first day of November.

  • [2017 TO 2017-11-01]: Start of 2017 until the end of the first day of November.

  • [* TO 2018-01-31]: From the earliest representable time through to the end of the day on 2018-01-31.

  • [2017-02-02T14:57:00 TO 2017-02-02T15:10:17]: From 14:57:00 to 15:10:17 on February 2, 2017.

If you specify a date instance using a date function, like currentDate(), you get the first millisecond of that day, not the entire day’s range.

The type name is case sensitive. Enclose the type name in single quotes when creating a table.

Duration

A duration value encodes three signed integers representing months, days, and nanoseconds. Months and days are 32-bit integers; nanoseconds is a 64-bit integer.

The duration type cannot be used in a primary key because ordering and equality are ambiguous without a date context (for example, it is not possible to determine whether 1mo is greater than or equal to 30d).

The following formats are accepted for duration values. Do not use quotes when inserting duration values.

Duration format
<N>y<N>mo<N>w<N>d<N>h<N>m<N>s<N>ms<N>us<N>ns

<N> is a quantity for the following units: y (years), mo (months), w (weeks), d (days), h (hours), m (minutes), s (seconds), ms (milliseconds), us or µs (microseconds), ns (nanoseconds). Omitted units default to 0.

For example, 12h30m is 12 hours and 30 minutes; 2mo10d12h is 2 months, 10 days, and 12 hours.

ISO 8601 full format
P<N>Y<N>M<N>DT<N>H<N>M<N>S

P starts the duration. T separates date units (Y years, M months, D days) from time units (H hours, M minutes, S seconds). Not all units are required. For example, 2M10D12H is 2 months, 10 days, and 12 hours, and P4Y6M3DT12H30M5S is 4 years, 6 months, 3 days, 12 hours, 30 minutes, and 5 seconds.

ISO 8601 duration syntax

The P designator starts every ISO 8601 duration and is always required. Values that appear after the P are date units. The T is only required if there are time units. Combine units freely on either side of T; any unit can be omitted when its value is zero. For example, P2Y6MT3H30M is 2 years, 6 months, 3 hours, and 30 minutes.

Designator Unit Example

P…​Y

Years

P6Y (6 years)

P…​M

Months (when before T)

P4M (4 months)

P…​D

Days

P1D (1 day)

T

Separator of date and time

Required before any time unit

T…​H

Hours

PT2H (2 hours)

T…​M

Minutes (when after T)

PT30M (30 minutes)

T…​S

Seconds

PT45S (45 seconds)
ISO 8601 alternative format
P[YYYY]-[MM]-[DD]T[hh]:[mm]:[ss]

Uses punctuation separators instead of unit designators. P starts the duration; T separates the date portion (YYYY-MM-DD) from the time portion (hh:mm:ss). All fields are required and must use the fixed digit widths shown.

For example, P0004-06-03T12:30:05 is four years, six months, three days, twelve hours, thirty minutes, and five seconds.

For example:

Insert a duration value
INSERT INTO cycling.race_times (
  finish_time, race_name, cyclist_name, race_date
) VALUES (
  1h4m48s20ms,
  '17th Santos Tour Down Under', 'Rohan DENNIS', '2017-04-14'
);
Update a duration value
UPDATE cycling.race_times SET finish_time = 2h5m22s14ms
WHERE race_name = '17th Santos Tour Down Under'
  AND cyclist_name = 'Rohan DENNIS'
  AND race_date = '2017-04-14';

Time

The time type is a 64-bit signed integer representing the number of nanoseconds since midnight with no corresponding date value.

The string format is '<hh>:<mm>:<ss>[.<fff>]', where milliseconds (<f>) are optional and cannot exceed 9 digits. When milliseconds are excluded or truncated, they are set to 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

For example, in an UPDATE:

UPDATE cycling.cyclist_races SET races[1] = { race_time : '14:30:00'}
  WHERE id = 5b6962dd-3f90-4c93-8f61-eabfa4a803e2 ;

Timestamp

A timestamp is a 64-bit signed integer combining date and time with the addition of an optional 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.

The following table shows the accepted timestamp patterns and examples. Timezone tokens z/zz/zzz accept named timezone abbreviations (for example, UTC, CEST, GMT-07:00). Tokens X/XX/XXX accept ISO 8601 offset notation: X uses hours only (for example, -07 or Z for UTC), XX uses +HHMM (for example, +0200), and XXX uses +HH:MM (for example, +02:00). All three variants of z produce identical output; use any one of them.

Pattern Example

yyyy-mm-dd

2018-04-26

yyyy-mm-dd HH:MM

2018-04-26 12:59

yyyy-mm-dd HH:MM:SS

2018-04-26 12:59:38

yyyy-mm-dd HH:MM:SS.SSS

Used to format timestamps as JSON, for example with SELECT JSON.

2018-04-26 12:59:38.226

yyyy-mm-dd’T’HH:MM

2018-04-26T12:59

yyyy-mm-dd’T’HH:MM:SS

2018-04-26T12:59:38

yyyy-mm-dd’T’HH:MM:SS.SSS

2018-04-26T12:59:38.226

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

Any pattern above followed by a timezone token (z, XX, XXX, etc.)

2018-04-26T12:59:38 UTC

2018-04-26T12:59:38+0200

2018-04-26T12:59:38+02:00

See also

Was this helpful?

Give Feedback

How can we improve the documentation?

© Copyright IBM Corporation 2026 | 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: Contact IBM