I'm transferring data from one clickhouse server to another one and faced problem with data filtering and timezone doesn't look correct. For simplicity lets call these servers server A and server B.
Server A has following response for time functions : timezone() = Europe/Moscow , now() = 2023-04-13 10:39:25 Server B returns same data for these functions. Both servers actually do not return Europe/Moscow time , they return UTC time (2023-04-13 10:39:25 is UTC and 2023-04-13 13:39:25 is correct Europe/Moscow time
Server A table has following timeseries column - _timestamp (DateTime64(3))
Server B table has following timeseries column - _timestamp (DateTime64(3))
I'm transfering data like following
INSERT INTO TABLE B (LIST OF CLUMNS)
SELECT
(LIST OF COLUMNS)
from remote('server A',databasenameA.tablenameA,'user','password')
where _timestamp >= '2022-09-01 00:00:00' and _timestamp < '2023-02-01 00:00:00'
Actually this script takes data in range >= '2022-08-31 21:00:00' and < '2023-01-31 21:00:00'
I can prove this if i take min and max date in target table after loading
select min(_timestamp) from TABLE_A = 2022-08-31 21:00:00
select max(_timestamp) from TABLE_A = 2023-01-31 20:59:59
Why despite on correct timezone() = Europe/Moscow i receive incorrect datetime (actually UTC time). Why despite on filtering conditions >= '2022-09-01 00:00:00' and < '2023-02-01 00:00:00' clickhouse convert these date conditions to another values. I'm using DBeaver to run these stateements and do not have direct access to server machine.
When you query table like this
select min(_timestamp) from TABLE_A
You get a value rendered into the text by DBeaver. It's not the orignal datetime timestamp, but the UInt32 value converted to Text by JVM using your current timezone at your desktop.
I suggest to use toUnixTimestamp to avoid confusion.
select toUnixTimestamp(min(_timestamp)) as min_timestamp from TABLE_A
And use that min_timestamp as a filter predicate in the INSERT SELECT.