How to localize a naïf timestamp before inserting into an UTC column

[polmonso]

Company or project name

SomEnergia

Question

We have many data sources in clickhouse with naïf columns year, month, day, hour, minute, second and a timezone column which specifies in which timezone each naïf timestamp happened.

We want to now convert this columns into a single UTC column and we're facing several roadblocks due to the time functions not supporting dynamic timezone.

Received exception from server (version 25.5.2):
Code: 44. DB::Exception: Received from localhost:9000. DB::Exception: A value of illegal type was provided as 6th argument 'timezone' to function 'makeDateTime'. Expected: const String, got: String: In scope SELECT *, makeDateTime(2024, 3, 1, 0, 0, 0, timezone) FROM some_data LIMIT 1. (ILLEGAL_COLUMN)

We've seen references to this problem in #6948, althought it focuses more on how the value is displayed. In the issue commentators suggest using toString which supports non-constant timezones, but it feels fragile and probably less performant.

It's a key point that we're not trying to mix timezones in a single column, localizing the timestamp is a necessary step to get the utc value. We're wondering if there's another way to do this, since it should be fairly common when dealing with naïve timestamps.

We've checked the functions in the documentation but we didn't find a function other than toString and the like that supports non-constant timezones.

We ended up doing the the mixing ourselves to circumvent the limitations of the time functions, like so:

      if(
        timezone = 'Atlantic/Canary',
        makeDateTime(year, month, day, hour, minute, second, 'Atlantic/Canary')::DateTime('UTC'),
        makeDateTime(year, month, day, hour, minute, second, 'Europe/Madrid')::DateTime('UTC')
      ) AS ts_utc,

And we would need a MultiIf if we had more timezones, specifying up to all the hundred and so iana timezones in a huge multiif statement.

What is the proper way to get the utc timestamps of naïf timestamps in Clickhouse ? Should we open a merge request or see if we could support dynamic timezones in at least some functions other than toString?

[yariks5s]

Hi @polmonso
Currently, ClickHouse does not support dynamic timezones in makeDateTime. The timezone must be a constant string. The most efficient workaround seems to be using a multiIf statement or converting timestamps to UTC. ToString will be also good in terms of the query simplicity, but it will be obviously slower.

If you can open a pull request with changes allowing the use of dynamic timezones there, feel free to assign me. If not, I will do it shortly.

[polmonso]

Noted! I'll take a look at the codebase to see how difficult would it be.

or converting timestamps to UTC

How were you imagining this? Because that's precisely what we're trying to do in ClickHouse, converting a naïf timestamp to UTC, but can't because time functions do not support dynamic timezones.
Manually deducing the offset and subtracting it to the "hours" column somehow? I'm curious on what you had in mind.

[yariks5s]

I meant the toString function, converting a timestamp as a string

[polmonso]

Hello @yariks5s1 You've labeled the issue as Completed meaning wontfix or that somebody managed to put on the hours before me and there's a PR submitted and merged?

[yariks5s]

Yep, sorry, this is my fault. I was looking through the issues assigned to me, and I thought that this issue was a question and not an issue about a new logic. I will reopen it.

[enqueue]

We are in a similar situation where we only have local timestamps and locations. We have dictionary for location to time zone, but it's currently impossible to parse the timestamps correctly.

I created a dbt macro to generate multiIf statement for all relevant time zones, but it is not nice.

Be aware that parsing without time zone, i.e. using server time zone, and then doing magic with offsets of actual time zone is error-prone, particularly on DST borders.