Date & Time Types

Synopsis

Datetime data types are used to specify data of date and time at a timezone, DATE for a specific day, TIME for time of day, and TIMESTAMP for the combination of both date and time.

Syntax

  1. type_specification ::= TIMESTAMP | DATE | TIME
  3. timestamp_format ::= date_format [ time_format ] [ timezone_format ]
  4. date_format ::= digit digit digit digit '-' digit [ digit ] '-' digit [ digit ]
  5. time_format ::= digit [ digit ] [ ':' digit [ digit ] [ ':' digit [digit]  [ '.' digit [ digit [ digit ] ] ] ] ] 
  6. timezone_format ::= [ 'UTC' ] ( '+' | '-' ) digit [ digit ] ':' digit [ digit ]

Where

  • the timestamp_format given above is not the timestamp literal but is used to match text literals when converting them to TIMESTAMP type.

Semantics

  • Columns of type DATE, TIME and TIMESTAMP can be part of the PRIMARY KEY.
  • Implicitly, value of type datetime type are neither convertible nor comparable to other data types.
  • Values of integer and text data types with the correct format (given above) are convertible to datetime types.
  • Supported timestamp range is from year 1900 to year 9999.
  • If not specified, the default value for hour, minute, second, and millisecond components is 0.
  • If not specified, the default timezone is UTC.

Examples

Using the date and type types

  1. cqlsh:example> CREATE TABLE orders(customer_id INT, order_date DATE, order_time TIME, amount DECIMAL, PRIMARY KEY ((customer_id), order_date, order_time));

Date and time values can be inserted using currentdate and currenttime standard functions.

  1. cqlsh:example> INSERT INTO orders(customer_id, order_date, order_time, amount) VALUES (1, currentdate(), currenttime(), 85.99);
  1. cqlsh:example> INSERT INTO orders(customer_id, order_date, order_time, amount) VALUES (1, currentdate(), currenttime(), 34.15);
  1. cqlsh:example> INSERT INTO orders(customer_id, order_date, order_time, amount) VALUES (2, currentdate(), currenttime(), 55.45);
  1. cqlsh:example> SELECT * FROM orders;
  1.  customer_id | order_date | order_time         | amount
  2. -------------+------------+--------------------+--------
  3.            1 | 2018-10-09 | 17:12:25.824094000 |  85.99
  4.            1 | 2018-10-09 | 17:12:56.350031000 |  34.15
  5.            2 | 2018-10-09 | 17:13:15.203633000 |  55.45

Date values can be given using date-time literals.

  1. cqlsh:example> SELECT sum(amount) FROM orders WHERE customer_id = 1 AND order_date = '2018-10-09';

  1.  system.sum(amount)

  1.          120.14

Using the timestamp type

You can do this as shown below.

  1. cqlsh:example> CREATE TABLE sensor_data(sensor_id INT, ts TIMESTAMP, value FLOAT, PRIMARY KEY(sensor_id, ts));

Timestamp values can be given using date-time literals.

  1. cqlsh:example> INSERT INTO sensor_data(sensor_id, ts, value) VALUES (1, '2017-07-04 12:30:30 UTC', 12.5);
  1. cqlsh:example> INSERT INTO sensor_data(sensor_id, ts, value) VALUES (1, '2017-07-04 12:31 UTC', 13.5);

Timestamp values can also be given as integers (milliseconds from epoch).

  1. cqlsh:example> INSERT INTO sensor_data(sensor_id, ts, value) VALUES (2, 1499171430000, 20);
  1. cqlsh:example> SELECT * FROM sensor_data;
  1.  sensor_id | ts                              | value
  2. -----------+---------------------------------+-------
  3.          2 | 2017-07-04 12:30:30.000000+0000 |    20
  4.          1 | 2017-07-04 12:30:30.000000+0000 |  12.5
  5.          1 | 2017-07-04 12:31:00.000000+0000 |  13.5

Supported timestamp literals

  1. '1992-06-04 12:30'
  2. '1992-6-4 12:30'
  3. '1992-06-04 12:30+04:00'
  4. '1992-6-4 12:30-04:30'
  5. '1992-06-04 12:30 UTC+04:00'
  6. '1992-6-4 12:30 UTC-04:30'
  7. '1992-06-04 12:30.321'
  8. '1992-6-4 12:30.12'
  9. '1992-06-04 12:30.321+04:00'
  10. '1992-6-4 12:30.12-04:30'
  11. '1992-06-04 12:30.321 UTC+04:00'
  12. '1992-6-4 12:30.12 UTC-04:30'
  13. '1992-06-04 12:30:45'
  14. '1992-6-4 12:30:45'
  15. '1992-06-04 12:30:45+04:00'
  16. '1992-6-4 12:30:45-04:30'
  17. '1992-06-04 12:30:45 UTC+04:00'
  18. '1992-6-4 12:30:45 UTC-04:30'
  19. '1992-06-04 12:30:45.321'
  20. '1992-6-4 12:30:45.12'
  21. '1992-06-04 12:30:45.321+04:00'
  22. '1992-6-4 12:30:45.12-04:30'
  23. '1992-06-04 12:30:45.321 UTC+04:00'
  24. '1992-6-4 12:30:45.12 UTC-04:30'
  25. '1992-06-04T12:30'
  26. '1992-6-4T12:30'
  27. '1992-06-04T12:30+04:00'
  28. '1992-6-4T12:30-04:30'
  29. '1992-06-04T12:30 UTC+04:00'
  30. '1992-6-4T12:30TUTC-04:30'
  31. '1992-06-04T12:30.321'
  32. '1992-6-4T12:30.12'
  33. '1992-06-04T12:30.321+04:00'
  34. '1992-6-4T12:30.12-04:30'
  35. '1992-06-04T12:30.321 UTC+04:00'
  36. '1992-6-4T12:30.12 UTC-04:30'
  37. '1992-06-04T12:30:45'
  38. '1992-6-4T12:30:45'
  39. '1992-06-04T12:30:45+04:00'
  40. '1992-6-4T12:30:45-04:30'
  41. '1992-06-04T12:30:45 UTC+04:00'
  42. '1992-6-4T12:30:45 UTC-04:30'
  43. '1992-06-04T12:30:45.321'
  44. '1992-6-4T12:30:45.12'
  45. '1992-06-04T12:30:45.321+04:00'
  46. '1992-6-4T12:30:45.12-04:30'
  47. '1992-06-04T12:30:45.321 UTC+04:00'
  48. '1992-6-4T12:30:45.12 UTC-04:30'
  49. '1992-06-04'
  50. '1992-6-4'
  51. '1992-06-04+04:00'
  52. '1992-6-4-04:30'
  53. '1992-06-04 UTC+04:00'
  54. '1992-6-4 UTC-04:30'

See also