Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Better timezone functionalities #10368

Open
Tracked by #8282
Abdullahsab3 opened this issue May 3, 2024 · 6 comments
Open
Tracked by #8282

Better timezone functionalities #10368

Abdullahsab3 opened this issue May 3, 2024 · 6 comments
Labels
enhancement New feature or request

Comments

@Abdullahsab3
Copy link
Contributor

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

@Abdullahsab3 Abdullahsab3 added the enhancement New feature or request label May 3, 2024
@jayzhan211
Copy link
Contributor

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
Copy link
Contributor

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
Copy link
Contributor Author

Abdullahsab3 commented May 6, 2024

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
Copy link
Contributor

alamb commented May 8, 2024

I haven't read this ticket in detail yet, but there is a collection of other issues on #8282

@mhilton
Copy link
Contributor

mhilton commented May 15, 2024

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
Copy link
Contributor

alamb commented May 21, 2024

I have filed #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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
enhancement New feature or request
Projects
None yet
Development

No branches or pull requests

4 participants