TIME and TIME WITH TIMEZONE do not respect DST

[kgpai]

Your Environment

• Presto version used: 0.293

Expected Behavior

TIME and TIME WITH TIMEZONE should respect day light savings time based on the timezone in effect.

presto:default> select TIME '00:00 America/Los_Angeles' - TIME '00:00 UTC';
     _col0            
----------------
 0 07:00:00.000 

localtime and current_time should also respect DST if in a DST time zone.

presto:default> select localtime;
    _col0             
--------------
 11:17:10.819 
(1 row)


presto:default> select current_time;
              _col0               
----------------------------------
 11:18:27.494 America/Los_Angeles 
(1 row)

Current Behavior

TIME and TIME WITH TIMEZONE do not respect day light savings time . There are also other inconsistencies which I will list below.

presto:default> select TIME '00:00 America/Los_Angeles' - TIME '00:00 UTC';
     _col0            
----------------
 0 08:00:00.000 

However currently the time zone displacement between UTC and America/Los_Angeles is 7 hours and not 8.

This can be further illustrated by running localtime and current time in any DST timezone.
My current time is 11:17 PST.

presto:default> select localtime;
    _col0             
--------------
 10:17:10.819 
(1 row)

presto:default> select current_time;
              _col0               
----------------------------------
 10:18:27.494 America/Los_Angeles 
(1 row)

As you can see these results are off by 1 hour due to not account DST.

Another problem is when casting time to timestamp.

presto:default> select cast(current_time as timestamp);
          _col0          
-------------------------
 1970-01-01 10:51:52.253 
(1 row)

The above can be very misleading to a lot of consumers.

Possible Solution

TIME and TIME WITH TIMEZONE should take into account DST.
Proposed document which outlines behavior and proposed changes : https://docs.google.com/document/d/1m73xx2FqyoqWSH4DO2FO5yVdkSgKbtk51J3Nf_f9GXk/edit?tab=t.0 .

Context

Velox is working on implementing TIME and TIME with TIMEZONE here : facebookincubator/velox#14633 and we are looking to support it taking into account DST.

[kgpai]

cc: @kaikalur @rschlussel @tdcmeehan @amitkdutta @mbasmanova @majetideepak @czentgr

[mbasmanova]

CC: @aditi-pandit

[spershin]

This might be difficult to fix in Presto Java, since it has not been done over the years?

[imjalpreet]

It looks like TIME/TIME WITH TIMEZONE in Presto Java uses a fixed reference date (1970-01-01), which causes it to apply PST (UTC-8) instead of PDT (UTC-7).

[imjalpreet]

This makes me question whether we should be supporting named time zones with TIME or TIME WITH TIME ZONE. Since DST rules depend on the date, and these types only represent a time of day, there isn’t enough information to correctly apply those rules. Instead, we should only support fixed offsets, like -07:00 or +07:00.

[kgpai]

@imjalpreet The sql standard quoted below seems to imply that TIME and TIME with TIME ZONE deal with 'localtime' i.e with regards to current day.

For the convenience of users, whenever a datetime value with time zone is to be implicitly derived from one
without (for example, in a simple assignment operation), SQL assumes the value without time zone to be local,
subtracts the current default time zone displacement of the SQL-session from it to give UTC, and associates
that time zone displacement with the result.
Conversely, whenever a datetime value without time zone is to be implicitly derived from one with, SQL
assumes the value with time zone to be UTC, adds the time zone displacement to it to give local time, and the
result, without any time zone displacement, is local.

For e. g it says when casting TIME without TIME ZONE to TIMESTAMP it says we should "Copy date fields from
CURRENT_DATE and time fields from SV (Source Value)"

[tdcmeehan]

@imjalpreet that's exactly right. For the first example, as far as I'm aware, neither TIME nor TIME WITH TIME ZONE actually represent points in time, so I'm not sure direct comparisons of two times with timezones actually can take into account DST since they don't have the date component.

I think the problem is we're allowing the use of IANA timezone names, but the SQL standard doesn't allow for that format, it only specifies timezone offset numeric values:

<time literal> ::=
  TIME <time string>
<time string> ::=
  <quote> <unquoted time string> <quote>
<unquoted time string> ::=
  <time value> [ <time zone interval> ]
...
<time zone interval> ::=
  <sign> <hours value> <colon> <minutes value>
...
<hours value> ::=
  <datetime value>
<minutes value> ::=
  <datetime value>
...
<datetime value> ::=
  <unsigned integer>

With numeric values, there's no ambiguity anymore. This also tracks with Postgres, which also don't allow IANA timezone names.

I think the last example is problematic however, because the SQL standard actually does specify this behavior--it clearly says it should use the session's date component:

Copy date fields from CUR- RENT_DATE and time and time zone fields from SV

[imjalpreet]

So, it seems we should disallow named time zones and fix the cast to TIMESTAMP to use the current date instead of the fixed 1970-01-01, based on the SQL standard.

[kgpai]

@tdcmeehan

TIME nor TIME WITH TIME ZONE actually represent points in time, so I'm not sure direct comparisons of two times with timezones actually can take into account DST since they don't have the date component.

If we are taking into account CURRENT_DATE when converting to timestamp doesnt it stand to reason we should do that too when taking into account comparison of two TIMES and thus take into account DST. My understanding of the spec seems to imply that TIME is always relative to localtime (not the function) which maybe influenced by DST and hence we should take that into account else it can cause a lot of confusion and possibly misleading results.

[tdcmeehan]

@kgpai I don't think so, the reason being, there's no telling what date the time corresponds to. If the session's local date is desired (as you quoted in the spec), then there's explicit means to do that, but when comparing two TIME values without converting to a point in time value, then there's no information to determine DST conversion--it's dependent on the context, in the same way that TIMESTAMP [WITHOUT TIMEZONE] is.

[kgpai]

@tdcmeehan I think my point is we do have to take into account local context which includes CURRENT_DATE and timezone when dealing with TIME.
This is the reason why running current_time or localtime in a DST time zone gives an off by 1 hour error.
I am not sure how it is possible for current_time and localtime to give the same answer as the local clock without the above - If you know of some way please let me know . Or are we saying that we expect these two functions to be give off by 1 hour results based on where you are and what time of year it is ?