(Fiddle Link Available) ClickHouse changing column timezone by using a separate timezone column(string)

Question

Fiddle Link -> https://fiddle.clickhouse.com/6a18d469-7019-44d2-a65b-828f5c6be755

Example Clickhouse Schema

enter code here

DateTable

Date	Timezone
May 19, 2023, 04:00:00	Europe/London
May 19, 2023, 04:00:00	Asia/Kolkata

I can change timezone using the function toTimezone(date, 'Europe/London').

But I can't use toTimezone(date, timezone).

I want to run a query like this

select toTimezone(date, timezone) from DateTable

It says ClickHouse timezone conversion needs a const String. Any workaround over this would help a lot.

Answer 1

Only to hardcode all timezones.

CREATE TABLE DateTimeTest (`date` DateTime64(3, 'Asia/Kolkata'), timezone String) ENGINE=Memory;

INSERT INTO DateTimeTest VALUES ('2019-01-01 04:00:00', 'Asia/Kolkata');
INSERT INTO DateTimeTest VALUES ('2019-01-01 04:00:00', 'America/New_York');
INSERT INTO DateTimeTest VALUES ('2019-01-01 04:00:00', 'Asia/Kolkata');


SELECT
   multiIf(
     timezone='Asia/Kolkata',toString(date, 'Asia/Kolkata'),
     timezone='America/New_York',toString(date, 'America/New_York'),
     toString(date, 'UTC')
  ) r
FROM DateTimeTest;

┌─r───────────────────────┐
│ 2018-12-31 17:30:00.000 │
│ 2019-01-01 04:00:00.000 │
│ 2019-01-01 04:00:00.000 │
└─────────────────────────┘