[SOLVED] Trying to understand Trino and how it handles Time Zones

Trying to understand Trino and how it handles Time Zones

I'm observing strange behaviors when trying to convert timestamps between time zones in Trino. I believe it may be due to some conversions not behaving as expected. Perhaps someone can explain why CAST(current_timestamp as timestamp) observes the current time zone setting, but current_timestamp does not?

Example:

It is currently 4:08 PM Central Time (Chicago). When I run:

SET TIME ZONE 'America/Chicago';
SELECT 
  current_timezone() TZ
 ,current_timestamp TS
 ,CAST(current_timestamp as timestamp) CASTTS;

I get:
TZ: America/Chicago
TS: 2022-07-26 16:08:06
CASTTS: 2022-07-26 16:08:06

When I run:

SET TIME ZONE 'UTC';
SELECT 
  current_timezone() TZ
 ,current_timestamp TS
 ,CAST(current_timestamp as timestamp) CASTTS

I get:
TZ: UTC
TS: 2022-07-26 16:09:37
CASTTS: 2022-07-26 21:09:37

This is leading to additional issues when trying to convert a timestamp column from it's original timezone (UTC) to other timezones (Central/Eastern/etc)

Environment:

I'm in Central Time Zone.
Dbeaver 21.0.4
Trino 391 jdbc driver
Trino 380 (per SELECT VERSION())
Windows 10 Enterprise x64 21H2

Solution

This is a dbeaver issue. Dbeaver shows all times in local time zone, even if specified otherwise. To fix it, go to Window > Preferences > Editors > Data Editor > Data Formats and then check the box that says 'Disable date/time formatting'.