Union all select with different timezone get wrong result about clickhouse HOT 5 CLOSED

This is a correct and expected result.
Timezone is a property of a column not of a value. A column can have only one timezone.
The timezone from the first select is used.

from clickhouse.

Also 1714551897000 it's not a DateTime, it's DateTime64.

https://fiddle.clickhouse.com/498612af-5194-410c-85d0-ea7e00aba2ee

https://clickhouse.com/docs/en/sql-reference/functions/type-conversion-functions#fromunixtimestamp64milli

from clickhouse.

This is a correct and expected result. Timezone is a property of a column not of a value. A column can have only one timezone. The timezone from the first select is used.

Hi, thanks, but why use literal time string it's also right:

WITH '2024-05-01 16:24:57' AS date_value
SELECT
    'UTC-12',
    toDateTime(date_value, 'Etc/GMT+12')
UNION ALL
SELECT
    'UTC-11',
    toDateTime(date_value, 'Pacific/Midway')
UNION ALL
SELECT
    'UTC-10',
    toDateTime(date_value, 'Pacific/Honolulu')

Query id: 891b449f-c9b8-42de-929e-4990bf857c71

   ┌─'UTC-12'─┬─toDateTime(date_value, 'Etc/GMT+12')─┐
1. │ UTC-12   │                  2024-05-01 16:24:57 │
   └──────────┴──────────────────────────────────────┘
   ┌─'UTC-12'─┬─toDateTime(date_value, 'Etc/GMT+12')─┐
2. │ UTC-11   │                  2024-05-01 15:24:57 │
   └──────────┴──────────────────────────────────────┘
   ┌─'UTC-12'─┬─toDateTime(date_value, 'Etc/GMT+12')─┐
3. │ UTC-10   │                  2024-05-01 14:24:57 │
   └──────────┴──────────────────────────────────────┘

from clickhouse.

Hi, thanks, but why use literal time string it's also right:

Because TZ is property of datetime type and its value is numeric (timestamp - number of ticks since the beginning of the epoch in UTC). TZ is used during the parsing of strings and during the rendering numeric values back to strings.

So in case of toDateTime('2024-05-01 16:24:57', 'Pacific/Midway') -> string form '2024-05-01 16:24:57' is converted using Pacific/Midway and the result is type DateTime('Pacific/Midway').
And in case of toDateTime(1714551897, 'Pacific/Midway') -> numeric value is placed into a datetime value without using TZ with the type DateTime('Pacific/Midway').
In other words Clickhouse treats numbers 1714551897 as a fully prepared timestamp in UTC TZ. And strings '2024-05-01 16:24:57' as not prepared value which should be converted into a timestamp using TZ.

from clickhouse.

Got you, thanks.

from clickhouse.