Trino: Output omits time zone information when using CAST
Answered
When using a CAST of a timestamp querying a Trino data source, the timezone information is omitted in the Output and the output is converted to the current_timezone() value. This may confuse people and waste time on solving the issue.
SELECT
raw_ts
, CAST(raw_ts AS TIMESTAMP) AT TIME ZONE 'Europe/Berlin'
, CAST(raw_ts AS TIMESTAMP WITH TIME ZONE) AT TIME ZONE 'Europe/Berlin'
FROM
(SELECT TIMESTAMP '2012-10-31 01:00 Europe/Berlin' raw_ts) d;
Expectation would be to get
- _col1: 2012-10-31 02:00:00 +01:00 (because the timezone information got lost during the CAST to TIMESTAMP and its using current_timezone which is set to UTC)
- _col2: 2012-10-31 01:00:00 +01:00
Interesting enough - the information is stored correctly as using to_iso8601() is showing the correct values
SELECT
raw_ts
, to_iso8601(CAST(raw_ts AS TIMESTAMP) AT TIME ZONE 'Europe/Berlin')
, to_iso8601(CAST(raw_ts AS TIMESTAMP WITH TIME ZONE) AT TIME ZONE 'Europe/Berlin')
FROM
(SELECT TIMESTAMP '2012-10-31 01:00 Europe/Berlin' raw_ts) d;
Used system:
- DataGrip 2021.2.4 and IDEA 2021.2.3
- Trino driver 351.0
Using Presto driver 0.245.1 instead, the results show up as expected:
Can you please check this issue? Thanks
Please sign in to leave a comment.
Hello Matthias,

It's probably related to metadata returned by a driver.
Could you please tell us type names of all columns in your first example? To do it hover over column name in result view like shown on screenshot:
Hi Liudmila Kornilova,
thanks for looking into this. The data types of the first example are as follows:
Thanks!
I've found the cause of the problem
It will be fixed in v2021.3
Great to hear! Awesome support. Thanks a lot.