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 ]. Typically are used - 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 is not stored in the rows of the table (or in resultset), but is stored in the column metadata. See details in DateTime.
Supported range of values: [1925-01-01 00:00:00, 2283-11-11 23:59:59.99999999] (Note: The precision of the maximum value is 8).
Examplesβ
- Creating a table with
DateTime64
-type column and inserting data into it:
CREATE TABLE dt
(
`timestamp` DateTime64(3, 'Asia/Istanbul'),
`event_id` UInt8
)
ENGINE = TinyLog;
INSERT INTO dt Values (1546300800000, 1), ('2019-01-01 00:00:00', 2);
SELECT * FROM dt;
ββββββββββββββββtimestampββ¬βevent_idββ
β 2019-01-01 03:00:00.000 β 1 β
β 2019-01-01 00:00:00.000 β 2 β
βββββββββββββββββββββββββββ΄βββββββββββ
- When inserting datetime as an integer, it is treated as an appropriately scaled Unix Timestamp (UTC).
1546300800000
(with precision 3) represents'2019-01-01 00:00:00'
UTC. However, astimestamp
column hasAsia/Istanbul
(UTC+3) timezone specified, when outputting as a string the value will be shown as'2019-01-01 03:00:00'
. - 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 inAsia/Istanbul
timezone and stored as1546290000000
.
- Filtering on
DateTime64
values
SELECT * FROM dt WHERE timestamp = toDateTime64('2019-01-01 00:00:00', 3, 'Asia/Istanbul');
ββββββββββββββββtimestampββ¬βevent_idββ
β 2019-01-01 00:00:00.000 β 2 β
βββββββββββββββββββββββββββ΄βββββββββββ
Unlike DateTime
, DateTime64
values are not converted from String
automatically.
- Getting a time zone for a
DateTime64
-type value:
SELECT toDateTime64(now(), 3, 'Asia/Istanbul') AS column, toTypeName(column) AS x;
βββββββββββββββββββcolumnββ¬βxβββββββββββββββββββββββββββββββ
β 2019-10-16 04:12:04.000 β DateTime64(3, 'Asia/Istanbul') β
βββββββββββββββββββββββββββ΄βββββββββββββββββββββββββββββββββ
- Timezone conversion
SELECT
toDateTime64(timestamp, 3, 'Europe/London') as lon_time,
toDateTime64(timestamp, 3, 'Asia/Istanbul') as mos_time
FROM dt;
ββββββββββββββββlon_timeβββ¬ββββββββββββββββmos_timeββ
β 2019-01-01 00:00:00.000 β 2019-01-01 03:00:00.000 β
β 2018-12-31 21:00:00.000 β 2019-01-01 00:00:00.000 β
βββββββββββββββββββββββββββ΄ββββββββββββββββββββββββββ
See Also