CODEWITHSUNDEEP
sqlclickhouse
Captain Comic
Captain Comic

Reputation: 16186

Incorrect time returned from Clickhouse

I am observing strage thing in ClickHouse database

When I specify datetime in WHERE condition clichouse shifts my request by one hour. This happens with everytable that has DateTime type

My machine located in Moscow timezone UTC+3 Server is located in CET timezome UTC+1 Moscow has no daylight saving, but the problem appeared some time ago and I think it is because Europe shifted time by hour

Example, give me record between 23:59 and 00:19 datetime column has DateTime type

select datetime from ticker_arch
where
datetime <= '2020-11-23 00:10:00' and
datetime >= '2020-11-22 23:59:00'
order by datetime desc

result gives records between 22:59 and 23:10

2020-11-22 23:09:46
2020-11-22 23:09:46
2020-11-22 23:09:46
2020-11-22 23:09:46

This happens when I run query in Datagrip and from my code DBContext Please suggest

Upvotes: 0

Views: 1945

Answers (1)

Denny Crane
Denny Crane

Reputation: 13245

It's the expected behavior for CH JDBC driver.

JDBC driver converts DateTime values (to their string representations) from CH server timezone to local (for JVM) timezone.

https://github.com/ClickHouse/ClickHouse/issues/17387

The same for CH client:

# clickhouse-client --use_client_time_zone=1 -q "select now(), toString(now())"
2020-11-25 19:02:31 2020-11-25 20:02:31

# TZ=Europe/Moscow clickhouse-client --use_client_time_zone=1 -q "select now(), toString(now())"
2020-11-25 22:02:59 2020-11-25 20:02:59

now() -- rendered to String by a client with using localtimezone

toString(now()) -- rendered to String by a server using servertimezone

Upvotes: 1

Related Questions