Clickhouse Materialized DateTime column not showing time according to specified timezone

So i have a table having DDL

CREATE TABLE test_for_date
(
    `insert_time` DateTime64(3),
    `mat_time_utc` DateTime MATERIALIZED toDateTime(insert_time, 'Asia/Calcutta')
)
ENGINE = Log

inserting the data to table

insert into test_for_date(*) values('1629185516555')

When i select the columns from table i should expect insert_time as UTC and mat_time_utc as IST but both of the column are showing UTC time.

┌─────────────insert_time─┬────────mat_time_utc─┐
│ 2021-08-17 07:31:56.555 │ 2021-08-17 07:31:56 │
└─────────────────────────┴─────────────────────┘

But when i run the materialized column's logic in a seperate query, it is giving correct result

select toDateTime(insert_time, 'Asia/Calcutta') from test_for_date;

┌─toDateTime(insert_time, 'Asia/Calcutta')─┐
│                      2021-08-17 13:01:56 │
└──────────────────────────────────────────┘

I am not able to understand why is this happening. Internally how datetime is stored and how materialized column is executed.

P.S - Server timezone is 'Etc/UTC'

select timezone()
┌─timezone()─┐
│ Etc/UCT    │
└────────────┘

Solution 1:

The column mat_time_utc doesn't declare the timezone so it gets the server's default, which in your case is UTC. You can declare it with something like:

CREATE TABLE test_for_date
(
    `insert_time` DateTime64(3),
    `mat_time_utc` DateTime('Asia/Calcutta') MATERIALIZED toDateTime(insert_time)
)
ENGINE = Log

insert into test_for_date(*) values('1629185516555')

SELECT
    insert_time,
    mat_time_utc
FROM test_for_date

┌─────────────insert_time─┬────────mat_time_utc─┐
│ 2021-08-17 09:31:56.555 │ 2021-08-17 13:01:56 │
└─────────────────────────┴─────────────────────┘