Implement timezone as a session property that is able to be set by client

[minhancao]

Currently timezone is not a session property when running SHOW SESSION;. Looking to implement being able to set timezone as a session property.

Expected Behavior or Use Case

This will be useful when one is working with queries with timestamp with no timezone columns and want to get deterministic results when testing on machines with different timezones. Presto's current behavior when dealing with timestamp with no timezone column is to assume the current machine's timezone that the presto-cli is running on.

Presto Component, Service, or Connector

Session

Possible Implementation

Add timezone as a session property into presto-main/SystemSessionProperties.java and then have the query SET SESSION timezone = 'UTC'; for example to route to call setTimeZoneKey() in presto-main/Session.java

Example Screenshots (if appropriate):

Context

[yingsu00]

Example :

minhancao@minhans-mbp ~ % java -jar -Duser.timezone=UTC ./presto-cli --server https://engmcaob1252j.ibm.prestodb.dev/ --catalog hive --schema appnexus_ctas
presto:appnexus_ctas> select current_time;
      _col0       
------------------
 21:23:14.831 UTC 
(1 row)

Query 20240417_212314_00020_dezqt, FINISHED, 1 node
Splits: 17 total, 17 done (100.00%)
[Latency: client-side: 254ms, server-side: 82ms] [0 rows, 0B] [0 rows/s, 0B/s]

presto:appnexus_ctas> exit
minhancao@minhans-mbp ~ % ./presto-cli --server https://engmcaob1252j.ibm.prestodb.dev/ --catalog hive --schema appnexus_ctas
presto:appnexus_ctas> select current_time;
              _col0               
----------------------------------
 13:23:26.195 America/Los_Angeles 
(1 row)

Query 20240417_212326_00023_dezqt, FINISHED, 1 node
Splits: 17 total, 17 done (100.00%)
[Latency: client-side: 250ms, server-side: 80ms] [0 rows, 0B] [0 rows/s, 0B/s]

cc @tdcmeehan

[tdcmeehan]

If the desire is for deterministic testing, then can't one simply set the timezone in the client?

[minhancao]

@tdcmeehan If we implement it in Presto as a session property, you can use it on presto-cli, presto c# driver, or any other driver, but this jvm way only works for java-based solution

[tdcmeehan]

The timezone of the user is already a part of the session that is added through the X-Presto-Time-Zone header, so any client could set it as they wish. It is not currently only Java-based.

[yzhang1991]

If we make this timezone a session parameter, we will see its value from query JSONs, Presto UI, etc. If this is set using an HTTP header value, then there is no way we can see what time zone was used when running a query, which can be confusing when people need to troubleshoot. If we really do not want to expose this in the form of a session parameter, we probably should expose its value in the query JSON also Presto UI. @tdcmeehan

[tdcmeehan]

Because the timeZoneKey is part of the session representation, it technically is part of the query JSON, it's just that we only show the key which isn't human readable. I think we can modify the session representation to also have a read-only field timeZone that is the human-readable abbreviated timezone. We can also add this to the UI.

[yzhang1991]

@tdcmeehan That sounds good to me! Is this something that @yhwang can help us get?

[yzhang1991]

Hi @yhwang, is there an issue for this work?

[yhwang]

@yzhang1991 nope, please feel free to open one and tag me. To clarify, the goal is to display the session.timezoneKey in the UI, right?