DateTime Types

AttentionThis page documents an earlier version. Go to the latest (v2.1)version.

Synopsis

Datetime datatypes 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.Of the three, Yugabyte currently only supports the TIMESTAMP type.

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 TIMESTAMP can be part of the PRIMARY KEY.
  • Implicitly, value of type datetime type are neither convertible nor comparable to other datatypes.
  • Values of integer and text datatypes 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 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));
  1. cqlsh:example> -- 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

Date and Time FunctionsData Types