DateTime64

Allows to store an instant in time, that can be expressed as a calendar date and a time of a day, with defined sub-second precision

Tick size (precision): 10^(-precision) seconds. Valid range: [ 0 : 9 ].
Typical values: 3 (milliseconds), 6 (microseconds), 9 (nanoseconds).

Syntax

DateTime64(precision, [timezone])

Internally, stores data as a number of ‘ticks’ since epoch start (1970-01-01 00:00:00 UTC) as Int64. The tick resolution is determined by the precision parameter. Additionally, the DateTime64 type can store time zone that is the same for the entire column, that affects how the values of the DateTime64 type values are displayed in text format and how the values specified as strings are parsed (‘2020-01-01 05:00:01.000’). The time zone isn't stored in the rows of the table (or in resultset), but is stored in the column metadata.

Supported range of values: [1900-01-01 00:00:00, 2299-12-31 23:59:59.99999999]

Note: The precision of the maximum value is 8. If the maximum precision of 9 digits (nanoseconds) is used, the maximum supported value is 2262-04-11 23:47:16 in UTC.

Examples

  1. Creating a table with DateTime64-type column and inserting data into it:
dt64.datasource
SCHEMA >
    timestamp DateTime64(3, 'Asia/Istanbul') `json:$.ts`
    event_id UInt8 `json:$.event_id`

ENGINE "MergeTree"

tb push datasources/dt64.datasource

echo '{"ts":1546300800123,"event_id":1}' > dt64.ndjson
echo '{"ts":1546300800.123,"event_id":2}' >> dt64.ndjson
echo '{"ts":"2019-01-01 00:00:00","event_id":3}' >> dt64.ndjson

tb datasource append dt64 dt64.ndjson

tb sql "SELECT * FROM dt64"
--------------------------------------
| timestamp               | event_id |
--------------------------------------
| 2019-01-01 03:00:00.000 |        3 |
| 2019-01-01 03:00:00.123 |        1 |
| 2019-01-01 03:00:00.123 |        2 |
--------------------------------------
  • When inserting datetime as an integer, it's treated as an appropriately scaled Unix Timestamp (UTC). 1546300800000 (with precision 3) represents '2019-01-01 00:00:00' UTC. However, as timestamp column has Asia/Istanbul (UTC+3) timezone specified, when outputting as a string the value will be shown as '2019-01-01 03:00:00'. Inserting datetime as a decimal will treat it similarly as an integer, except the value before the decimal point is the Unix Timestamp up to and including the seconds, and after the decimal point will be treated as the precision.
  • When inserting string value as datetime, it's treated as being in column timezone. '2019-01-01 00:00:00' will be treated as being in Asia/Istanbul timezone and stored as 1546290000000.
  1. Filtering on DateTime64 values
SELECT * FROM dt64 WHERE timestamp = toDateTime64('2019-01-01 03:00:00', 3, 'Asia/Istanbul')

--------------------------------------
| timestamp               | event_id |
--------------------------------------
| 2019-01-01 03:00:00.000 |        3 |
--------------------------------------

Unlike DateTime, DateTime64 values aren't converted from String automatically.

SELECT * FROM dt64 WHERE timestamp = toDateTime64(1546300800.123, 3)

--------------------------------------
| timestamp               | event_id |
--------------------------------------
| 2019-01-01 03:00:00.123 |        1 |
| 2019-01-01 03:00:00.123 |        2 |
--------------------------------------

Contrary to inserting, the toDateTime64 function will treat all values as the decimal variant, so precision needs to be given after the decimal point.

  1. Getting a time zone for a DateTime64-type value:
SELECT toDateTime64(now(), 3, 'Asia/Istanbul') AS column, toTypeName(column) AS x

------------------------------------------------------------
| column                  | x                              |
------------------------------------------------------------
| 2024-12-26 18:07:25.000 | DateTime64(3, 'Asia/Istanbul') |
------------------------------------------------------------
  1. Timezone conversion
SELECT
toDateTime64(timestamp, 3, 'Europe/London') as lon_time,
toDateTime64(timestamp, 3, 'Asia/Istanbul') as istanbul_time
FROM dt64

-----------------------------------------------------
| lon_time                | istanbul_time           |
-----------------------------------------------------
| 2019-01-01 00:00:00.000 | 2019-01-01 03:00:00.000 |
| 2019-01-01 00:00:00.123 | 2019-01-01 03:00:00.123 |
| 2019-01-01 00:00:00.123 | 2019-01-01 03:00:00.123 |
-----------------------------------------------------
Updated