Upon executing the following query in my ClickHouse database:
SELECT formatDateTime(now(), '%Y-%m-%d %H:%M:%S', 'Asia/Tehran');
I received an unexpected result:
2024-03-29 00:34:35
However, the expected output was:
2024-03-28 23:34:35
When setting the timezone in ClickHouse to Asia/Tehran, all datetime values are adjusted to UTC+04:30 instead of the expected UTC+03:30. Could you provide guidance on how to address this issue?
On
Iran used to observe daylight saving time, but it was abolished in 2023. The corresponding changes for the Asia/Tehran time zone were applied in the IANA TZ data, version 2022b.
If you're seeing a +04:30 offset in 2023 or later, then the system you're running on does not have updated time zone data, and thus needs to be updated.
How to update the time zone data depends on how you've installed ClickHouse. For example, if you're just running their "quick install", on your own Linux machine, then you'd run:
apt-get -y tzdata
(That command could vary by Linux distro.)
If however, you're using a Docker image, you might need to update your dockerfile to include that command, as seen here.
The general best practice is always to keep time zone data current. Otherwise, you'll be applying the previous rules instead of the current ones, giving potentially incorrect results (as you described).
I have upgraded the ClickHouse version from 22.6.1.1985 to 24.3.1.2672, which has resolved the issue. This is due to the fact that prior to the update, ClickHouse (in its 2022 version) adjusted the Asia/Tehran timezone to UTC+4:30 after March 20th to accommodate daylight saving time. However, post-2022, Iran discontinued the observance of daylight saving time. Consequently, following the update, the Asia/Tehran timezone has reverted to UTC+3:30 after March 20th, as it was before the change and it considered by Clickhouse in latest version.