DateTime64

DateTime64(precision, [timezone])

​

Examples

1. Creating a table with DateTime64-type column and insert data into it:

CREATE TABLE dt64
(
    `timestamp` DateTime64(3, 'Asia/Istanbul'),
    `event_id` UInt8
)
ENGINE = MergeTree;

-- Parse DateTime64
-- - from an integer interpreted as the number of seconds since 1970-01-01 (like DateTime),
-- - from a decimal interpreted as the number of seconds, the fractional part giving sub-second precision,
-- - from a string.

INSERT INTO dt64
VALUES
(1546300800, 1),
(1546300800.123, 2),
('2019-01-01 00:00:00', 3);

SELECT * FROM dt64;

┌───────────────timestamp─┬─event_id─┐
│ 2019-01-01 03:00:00.000 │        1 │
│ 2019-01-01 03:00:00.123 │        2 │
│ 2019-01-01 00:00:00.000 │        3 │
└─────────────────────────┴──────────┘

• When inserting datetime as a number, it is treated as a Unix Timestamp (UTC) in seconds, like DateTime. 1546300800 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 a number with a fractional part works the same way: the part before the decimal point is the Unix Timestamp in seconds and the part after it provides sub-second precision according to the column’s precision. (Before version 26.8, a bare unquoted integer in the JSON and Values/Quoted input paths — the latter covering every format that parses fields with the Quoted escaping rule: Values, MySQLDump, and Template/CustomSeparated/Regexp configured with Quoted field escaping — was instead interpreted as the raw underlying value at the column precision, so 1546300800000 at precision 3 meant '2019-01-01 00:00:00'. To restore the previous behavior in these paths, set input_format_read_datetime_number_as_raw_value = 1 (or SET compatibility = '26.7'); this also affects the JSONExtract function and the JSON data type. The compatibility setting governs only a bare integer: in the Values format a fractional number, which the legacy streaming parser rejects, falls back to SQL expression evaluation and is read as seconds — the same as in versions before 26.8. In JSONExtract and the JSON data type a fractional value is parsed through Float64, so a timestamp with more digits than Float64 preserves can round to the adjacent value, unlike the row input formats which parse the original text exactly. The tab-separated, CSV and other escaped text input formats are not governed by this setting and keep their existing interpretation of an unquoted number: a large value is read as ticks.)
• When inserting string value as datetime, it is 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.

2. Filtering on DateTime64 values

SELECT * FROM dt64 WHERE timestamp = toDateTime64('2019-01-01 00:00:00', 3, 'Asia/Istanbul');

┌───────────────timestamp─┬─event_id─┐
│ 2019-01-01 00:00:00.000 │        3 │
└─────────────────────────┴──────────┘

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 │
└─────────────────────────┴──────────┘

3. Getting a time zone for a DateTime64-type value:

SELECT toDateTime64(now(), 3, 'Asia/Istanbul') AS column, toTypeName(column) AS x;

┌──────────────────column─┬─x──────────────────────────────┐
│ 2023-06-05 00:09:52.000 │ DateTime64(3, 'Asia/Istanbul') │
└─────────────────────────┴────────────────────────────────┘

4. 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.123 │ 2019-01-01 03:00:00.123 │
│ 2019-01-01 00:00:00.123 │ 2019-01-01 03:00:00.123 │
│ 2018-12-31 21:00:00.000 │ 2019-01-01 00:00:00.000 │
└─────────────────────────┴─────────────────────────┘

• Type conversion functions
• Functions for working with dates and times
• The date_time_input_format setting
• The date_time_output_format setting
• The timezone server configuration parameter
• The session_timezone setting
• Operators for working with dates and times
• Date data type
• DateTime data type