DateTime in ClickHouse refers to the number of seconds since 1970-01-01 00:00:00 GMT or UNIX timestamp.
Both on the server (for HTTP clients and certain types of queries, such toString(ts)) and on the client (for native clients) are capable of converting the UNIX timestamp into a human-readable format and vice versa.
Rules from several time zones may apply depending on where the conversion occurred.
Clickhouse-client, by default, tries to use the server timezone (see also –use_client_time_zone flag); you can verify the server timezone using the SELECT timezone().
The timestamp -> human-readable time rules of that timezone will be implemented if the timezone name is stored inside the data type.
SELECT
timezone(),
toDateTime(now()) AS t,
toTypeName(t),
toDateTime(now(), 'UTC') AS t_utc,
toTypeName(t_utc),
toUnixTimestamp(t),
toUnixTimestamp(t_utc)
FORMAT Vertical
Query id: e6eeb7ab-8491-44af-804a-85d5cea1fc60
Row 1:
──────
timezone(): Etc/UTC
t: 2023-07-06 11:52:40
toTypeName(toDateTime(now())): DateTime
t_utc: 2023-07-06 11:52:40
toTypeName(toDateTime(now(), 'UTC')): DateTime('UTC')
toUnixTimestamp(toDateTime(now())): 1688644360
toUnixTimestamp(toDateTime(now(), 'UTC')): 1688644360
1 row in set. Elapsed: 0.015 sec.
Clickhouse employs embedded tzdata since version 20.4 (see https://github.com/ClickHouse/ClickHouse/pull/10425).
You get acclimated to the tzdata version.
SELECT * FROM system.build_options WHERE name = 'TZDATA_VERSION' Query id: 92ecb6f5-8833-43d5-a49e-f5b09da00a8d ┌─name───────────┬─value─┐ │ TZDATA_VERSION │ 2023c │ └────────────────┴───────┘ 1 row in set. Elapsed: 0.003 sec.
And the list of available time zones
SELECT * FROM system.time_zones WHERE time_zone LIKE '%Europe%' Query id: 924ad9de-3c65-41c7-bf83-5c484ba1afd5 ┌─time_zone──────────┐ │ Europe/Amsterdam │ │ Europe/Andorra │ │ Europe/Astrakhan │ │ Europe/Athens │ │ Europe/Belfast │ │ Europe/Belgrade │ │ Europe/Berlin │ │ Europe/Bratislava │ │ Europe/Brussels │ │ Europe/Bucharest │ │ Europe/Budapest │ │ Europe/Busingen │ │ Europe/Chisinau │ │ Europe/Copenhagen │ │ Europe/Dublin │ │ Europe/Gibraltar │ │ Europe/Guernsey │ │ Europe/Helsinki │ │ Europe/Isle_of_Man │ │ Europe/Istanbul │ │ Europe/Jersey │ │ Europe/Kaliningrad │ │ Europe/Kiev │ │ Europe/Kirov │ │ Europe/Kyiv │ │ Europe/Lisbon │ │ Europe/Ljubljana │ │ Europe/London │ │ Europe/Luxembourg │ │ Europe/Madrid │ │ Europe/Malta │ │ Europe/Mariehamn │ │ Europe/Minsk │ │ Europe/Monaco │ │ Europe/Moscow │ │ Europe/Nicosia │ │ Europe/Oslo │ │ Europe/Paris │ │ Europe/Podgorica │ │ Europe/Prague │ │ Europe/Riga │ │ Europe/Rome │ │ Europe/Samara │ │ Europe/San_Marino │ │ Europe/Sarajevo │ │ Europe/Saratov │ │ Europe/Simferopol │ │ Europe/Skopje │ │ Europe/Sofia │ │ Europe/Stockholm │ │ Europe/Tallinn │ │ Europe/Tirane │ │ Europe/Tiraspol │ │ Europe/Ulyanovsk │ │ Europe/Uzhgorod │ │ Europe/Vaduz │ │ Europe/Vatican │ │ Europe/Vienna │ │ Europe/Vilnius │ │ Europe/Volgograd │ │ Europe/Warsaw │ │ Europe/Zagreb │ │ Europe/Zaporozhye │ │ Europe/Zurich │ └────────────────────┘
References: https://clickhouse.com/docs/en/operations/settings/settings#session_timezone