CODEWITHSUNDEEP
sqldatetimeselectwhere-clauseclickhouse
Alexandr
Alexandr

Reputation: 91

Unexpected Clickhouse datetime results

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.

Upvotes: 0

Views: 1017

Answers (1)

Denny Crane
Denny Crane

Reputation: 13300

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.

Upvotes: 1

Related Questions