意外的Clickhouse日期时间结果

huangapple go评论65阅读模式
英文:

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 transferring data like following:

INSERT INTO TABLE B (LIST OF COLUMNS)
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 the correct timezone() = Europe/Moscow, do I receive incorrect datetime (actually UTC time)? Why, despite the filtering conditions >= '2022-09-01 00:00:00' and < '2023-02-01 00:00:00', does ClickHouse convert these date conditions to other values? I'm using DBeaver to run these statements and do not have direct access to the server machine.

英文:

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(&#39;server A&#39;,databasenameA.tablenameA,&#39;user&#39;,&#39;password&#39;)
where _timestamp &gt;= &#39;2022-09-01 00:00:00&#39; and _timestamp &lt; &#39;2023-02-01 00:00:00&#39;

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.

答案1

得分: 1

当您查询表格如下所示:

select min(_timestamp) from TABLE_A

您会得到一个由DBeaver渲染成文本的值。这不是原始的日期时间戳,而是UInt32值,由JVM在您桌面上使用当前时区转换为文本。

我建议使用 toUnixTimestamp 来避免混淆。

select toUnixTimestamp(min(_timestamp)) as min_timestamp from TABLE_A

然后在 INSERT SELECT 中将 min_timestamp 用作筛选谓词。

英文:

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.

huangapple
  • 本文由 发表于 2023年4月13日 19:04:38
  • 转载请务必保留本文链接:https://go.coder-hub.com/76004677.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定