Better timezone functionalities about datafusion HOT 6 OPEN

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.

from datafusion.

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'

from datafusion.

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

from datafusion.

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

from datafusion.

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

from datafusion.

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

from datafusion.