Better timezone functionalities

[Abdullahsab3]

Is your feature request related to a problem or challenge?

It would be nice if Datafusion has support for better timezone functionalities. Though painful, local time is often used in energy analytics and data reporting (especially when the reporting is meant for official authorities).

Specifically, I would like to have the following functionalities:

• given a UTC timestamp, I would like to have that timestamp in local time of a given timezone
• given a UTC timestamp, data should be aggregated with local time from that UTC timestamp by providing a timezone
• Extracting the time offset (using date_part) for example, might be a nice to have
• Daylight savings hour should be handled correctly. chrono seems to prefer failing/returning None when there is ambigous results regarding daylight savings, which I assume is an inherited behavior for Arrow. If we can have some kind of flag to not fail, but either do something else , it would be great.

Describe the solution you'd like

I am not sure how this can be implemented in a generalized way. Perhaps by adding a function to_local_time that takes a timestamp/column as an argument, and a timezone as another argument. For example

> select to_local_time('2020-11-30T08:00:00.000Z'::timestamp, 'Europe/Brussels');
-- translates to '2020-11-30T08:00:00.000Z'::timestamp + interval '1 hour'
< '2020-11-30T09:00:00'

The difference between this and the at time zone operator is: The at time zone operator seems to be only adding timezone/offset information to the timestamp if I understood correctly. the proposed to_local_time function should ideally apply that offset to the provided timestamp. The same example above would be:

> select '2020-11-30T08:00:00.000Z'::timestamp at time zone 'Europe/Brussels';
+----------------------------------+
| Utf8("2020-11-30T08:00:00.000Z") |
+----------------------------------+
| 2020-11-30T08:00:00+01:00        |
+----------------------------------+
1 row in set. Query took 0.002 seconds.

Describe alternatives you've considered

I have currently a hacky alternative to aggregate using local time. What I do basically is the following:

• Get the time column
• Get the offset of the time using: ((time AT TIME ZONE 'UTC' at time zone 'Europe/Brussels') - (time AT TIME ZONE 'Europe/Brussels' at time zone 'UTC'))::interval
• • We need to consider the daylight savings hour here and treat it differently, given that (time AT TIME ZONE 'Europe/Brussels' at time zone 'UTC') fails during daylight savings hour. This then becomes for example:

  CASE 
    -- daylight savings in 2021. Dont add an offset
    WHEN time >= '2021-03-28T02:00:00' AND time < '2021-03-28T03:00:00' THEN interval '0'
    -- add the time offset for the 'Europe/Brussels' timezone
    ELSE ((time AT TIME ZONE 'UTC' at time zone 'Europe/Brussels') - (time AT TIME ZONE 'Europe/Brussels' at time zone 'UTC'))::interval
  END

The daylight savings predicates are dynamically generated in an application layer

• Add the offset to time

This all becomes something like:

SELECT 
  Sum(delta) AS value, 
DATE_BIN(
  interval '1 day', 
  time 
  + 
  CASE 
    -- daylight savings in 2021. Dont add an offset
    WHEN time >= '2021-03-28T02:00:00' AND time < '2021-03-28T03:00:00' THEN interval '0'
    -- add the time offset for the 'Europe/Brussels' timezone
    ELSE ((time AT TIME ZONE 'UTC' at time zone 'Europe/Brussels') - (time AT TIME ZONE 'Europe/Brussels' at time zone 'UTC'))::interval
  END)
FROM
...

Additional context

No response

[jayzhan211]

See the result in Postgres

postgres=# set timezone to '+08';
SET
postgres=# select '2020-11-30T08:00:00.000Z'::timestamp at time zone 'Europe/Brussels';
        timezone        
------------------------
 2020-11-30 15:00:00+08
(1 row)

postgres=# set timezone to '+01';
SET
postgres=# select '2020-11-30T08:00:00.000Z'::timestamp at time zone 'Europe/Brussels';
        timezone        
------------------------
 2020-11-30 08:00:00+01
(1 row)

postgres=# set timezone to '+00';
SET
postgres=# select '2020-11-30T08:00:00.000Z'::timestamp at time zone 'Europe/Brussels';
        timezone        
------------------------
 2020-11-30 07:00:00+00
(1 row)

I think we should not return 9:00:00, because the local time for timezone +1 is 8:00:00. If I understand correctly, the function you proposing is similar to the example above, but the timezone is given into the function?

so select to_local_time('2020-11-30T08:00:00.000Z'::timestamp, '+01'); should return 2020-11-30 08:00:00, select to_local_time('2020-11-30T08:00:00.000Z'::timestamp, '+00'); should return 2020-11-30 07:00:00 and likewise 2020-11-30 15:00:00 for '+08'

[jayzhan211]

btw, I think there might be some bug in datafusion, the behavior is unlike Postgres

statement ok
set datafusion.catalog.information_schema = true

statement ok
SET TIME ZONE = '+00:00'

query TT
SHOW TIMEZONE
----
datafusion.execution.time_zone +00:00

query P
select '2020-11-30T08:00:00.000Z'::timestamp at time zone 'Europe/Brussels';
----
2020-11-30T08:00:00+01:00

statement ok
SET TIME ZONE = '+01:00'

query P
select '2020-11-30T08:00:00.000Z'::timestamp at time zone 'Europe/Brussels';
----
2020-11-30T08:00:00+01:00

statement ok
SET TIME ZONE = '+08:00'

query P
select '2020-11-30T08:00:00.000Z'::timestamp at time zone 'Europe/Brussels';
----
2020-11-30T08:00:00+01:00

[Abdullahsab3]

Thanks for the response!

I think we should not return 9:00:00, because the local time for timezone +1 is 8:00:00.

I wonder whether it has to do with the at time zone operator. If we add at time zone 'UTC' in between for instance, we would be getting different results (in postgres):

select '2020-11-30T08:00:00.000Z'::timestamp at time zone 'UTC' at time zone 'Europe/Brussels';
---
2020-11-30 09:00:00.000000

This is the result I would expect

See: https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-ZONECONVERT I think the at time zone operator behaviour is to consider the timestamp as a timezone-less timestamp (which I think means that the Z will be ignored)

Now that I think about it, it might be that what I am proposing is actually for the at time zone operator to have the same behaviour as postgres. I also think that something is not right with the at time zone operator in Datafusion

[alamb]

I haven't read this ticket in detail yet, but there is a collection of other issues on https://github.com/apache/datafusion/issues/8282

[mhilton]

In postgresql the AT TIME ZONE expression changes a TIMESTAMP WITH TIME ZONE value to TIMESTAMP WITHOUT TIME ZONE value at the wall-clock time in the requested time zone (see table 9.34).

In datafusion this expression casts from one TIMESTAMP WITH TIME ZONE to another TIMESTAMP WITH TIME ZONE with the time zone adjusted.

[alamb]

I have filed https://github.com/apache/datafusion/issues/10602 with a summary of how I understand the usecase of "how do we bin timesamps in timezones with daylight savings time correctly" and some ideas on how to proceed