DateTime

Allows to store an instant in time, that can be expressed as a calendar date and a time of a day.

Syntax:

DateTime([timezone])

Supported range of values: [1970-01-01 00:00:00, 2106-02-07 06:28:15].

Resolution: 1 second.

Speed

The Date datatype is faster than DateTime under most conditions.

The Date type requires 2 bytes of storage, while DateTime requires 4. However, when the database compresses the database, this difference is amplified. This amplification is due to the minutes and seconds in DateTime being less compressible. Filtering and aggregating Date instead of DateTime is also faster.

Examples

Filter on DateTime values

SELECT * FROM dt WHERE timestamp = toDateTime('2019-01-01 00:00:00', 'Asia/Istanbul')

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

DateTime column values can be filtered using a string value in WHERE predicate. It will be converted to DateTime automatically:

SELECT * FROM dt WHERE timestamp = '2019-01-01 00:00:00'

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

Get a time zone for a DateTime-type column:

SELECT toDateTime(now(), 'Asia/Istanbul') AS column, toTypeName(column) AS x

┌──────────────column─┬─x─────────────────────────┐
│ 2019-10-16 04:12:04 │ DateTime('Asia/Istanbul') │
└─────────────────────┴───────────────────────────┘

Timezone conversion

SELECT
toDateTime(timestamp, 'Europe/London') as lon_time,
toDateTime(timestamp, 'Asia/Istanbul') as mos_time
FROM dt

┌───────────lon_time──┬────────────mos_time─┐
│ 2019-01-01 00:00:00 │ 2019-01-01 03:00:00 │
│ 2018-12-31 21:00:00 │ 2019-01-01 00:00:00 │
└─────────────────────┴─────────────────────┘
Updated