POC Support `date_bin` on timestamps with timezone #9

appletreeisyellow · 2024-05-28T23:27:29Z

This PR is a POC to support date_bin on timestamps with timezone. There are two things covered in this PR:

adds a UDF function to_local_time():
- only accept 1 input in type Timestamp(..., *)
- return in type Timestamp(..., None)
- for example:

> select to_local_time('2024-04-01T00:00:20Z'::timestamp AT TIME ZONE 'Europe/Brussels');
+---------------------------------------------+
| to_local_time(Utf8("2024-04-01T00:00:20Z")) |
+---------------------------------------------+
| 2024-04-01T00:00:20                         |
+---------------------------------------------+
1 row(s) fetched.
Elapsed 0.010 seconds.

> select to_local_time('2024-04-01T00:00:20'::timestamp AT TIME ZONE 'Europe/Brussels');
+--------------------------------------------+
| to_local_time(Utf8("2024-04-01T00:00:20")) |
+--------------------------------------------+
| 2024-04-01T00:00:20                        |
+--------------------------------------------+
1 row(s) fetched.
Elapsed 0.008 seconds.

> select
  time,
  arrow_typeof(time) as type,
  to_local_time(time) as to_local_time,
  arrow_typeof(to_local_time(time)) as to_local_time_type
from (
  select '2024-04-01T00:00:20Z'::timestamp AT TIME ZONE 'Europe/Brussels' as time
);
+---------------------------+------------------------------------------------+---------------------+-----------------------------+
| time                      | type                                           | to_local_time       | to_local_time_type          |
+---------------------------+------------------------------------------------+---------------------+-----------------------------+
| 2024-04-01T00:00:20+02:00 | Timestamp(Nanosecond, Some("Europe/Brussels")) | 2024-04-01T00:00:20 | Timestamp(Nanosecond, None) |
+---------------------------+------------------------------------------------+---------------------+-----------------------------+
1 row(s) fetched.
Elapsed 0.017 seconds.

Combine to_local_time() with date_bin() will look like:

> select date_bin(interval '1 day', to_local_time('2024-04-01T00:00:20Z'::timestamp AT TIME ZONE 'Europe/Brussels'));
+----------------------------------------------------------------------------------------------------+
| date_bin(IntervalMonthDayNano("18446744073709551616"),to_local_time(Utf8("2024-04-01T00:00:20Z"))) |
+----------------------------------------------------------------------------------------------------+
| 2024-04-01T00:00:00                                                                                |
+----------------------------------------------------------------------------------------------------+


> select date_bin(interval '1 day', to_local_time('2024-04-01T00:00:20Z'::timestamp AT TIME ZONE 'Europe/Brussels')) AT TIME ZONE 'Europe/Brussels';
+----------------------------------------------------------------------------------------------------+
| date_bin(IntervalMonthDayNano("18446744073709551616"),to_local_time(Utf8("2024-04-01T00:00:20Z"))) |
+----------------------------------------------------------------------------------------------------+
| 2024-04-01T00:00:00+02:00                                                                          |
+----------------------------------------------------------------------------------------------------+

Support date_bin on timestamps with timezone -- the original date_bin works correctly. the confusion happens at the display of the timestamp when there is a timezone. Even though the underlying time, the UTC time is correct, but when there is a timezone, the display automatically adjust the time, which result the display time to be wrong. Adjusting the underlaying time can make the final display time to our expected result.

appletreeisyellow · 2024-07-02T17:42:55Z

datafusion/functions/src/datetime/date_bin.rs

-                    "2020-09-08T00:00:00+05",
-                    "2020-09-08T00:00:00+05",
-                    "2020-09-08T00:00:00+05",
-                    "2020-09-08T00:00:00+05",
-                    "2020-09-08T00:00:00+05",
+                    "2020-09-07T19:00:00+05:00",
+                    "2020-09-07T19:00:00+05:00",
+                    "2020-09-07T19:00:00+05:00",
+                    "2020-09-07T19:00:00+05:00",
+                    "2020-09-07T19:00:00+05:00",


This is not right.. this is a change in behavior

appletreeisyellow · 2024-07-02T18:45:45Z

With the implementation in 54f1bdc, there is a change in behavior #9 (comment), which is not right.

Then I looked into Postgres to see how it works. This is what I found:

psql date_bin() displays the result either with timezone in UTC or without timezone. They don't display it in a different timezone, or maybe I'm missing something

postgres =# SELECT
  date_bin('1 d', '2021-10-31T01:00:00', '1970-01-01T00:00:00'),
  date_bin('1 d', '2021-10-31T01:00:00'::timestamp, '1970-01-01T00:00:00'),
  date_bin('1 d', '2021-10-31T01:00:00' AT TIME ZONE 'Europe/Brussels', '1970-01-01T00:00:00'),
  date_bin('1 d', '2021-10-31T01:00:00' AT TIME ZONE 'UTC' AT TIME ZONE 'Europe/Brussels', '1970-01-01T00:00:00'),
  date_bin('1 d', '2021-10-31T01:00:00', '1970-01-01T00:00:00') AT TIME ZONE 'Europe/Brussels',
  date_bin('1 d', '2021-10-31T01:00:00', '1970-01-01T00:00:00') AT TIME ZONE 'UTC' AT TIME ZONE 'Europe/Brussels'
;
        date_bin        |      date_bin       |      date_bin       |        date_bin        |      timezone       |        timezone
------------------------+---------------------+---------------------+------------------------+---------------------+------------------------
 2021-10-31 00:00:00+00 | 2021-10-31 00:00:00 | 2021-10-31 00:00:00 | 2021-10-30 00:00:00+00 | 2021-10-31 02:00:00 | 2021-10-30 22:00:00+00
(1 row)

This is very different from what DataFusion is currently doing, with cargo build from the current main branch:

> SELECT
  date_bin(interval '1 day', '2021-10-31T01:00:00', '1970-01-01T00:00:00') AS date_bin_1,
  date_bin(interval '1 day', '2021-10-31T01:00:00'::timestamp, '1970-01-01T00:00:00') AS date_bin_2,
  date_bin(interval '1 day', '2021-10-31T01:00:00' AT TIME ZONE 'Europe/Brussels', '1970-01-01T00:00:00') AS date_bin_3,
  date_bin(interval '1 day', '2021-10-31T01:00:00' AT TIME ZONE 'UTC' AT TIME ZONE 'Europe/Brussels', '1970-01-01T00:00:00') AS date_bin_4,
  date_bin(interval '1 day', '2021-10-31T01:00:00', '1970-01-01T00:00:00') AT TIME ZONE 'Europe/Brussels' AS date_bin_5,
  date_bin(interval '1 day', '2021-10-31T01:00:00', '1970-01-01T00:00:00') AT TIME ZONE 'UTC' AT TIME ZONE 'Europe/Brussels' AS date_bin_6
;
+---------------------+---------------------+---------------------------+---------------------------+---------------------------+---------------------------+
| date_bin_1          | date_bin_2          | date_bin_3                | date_bin_4                | date_bin_5                | date_bin_6                |
+---------------------+---------------------+---------------------------+---------------------------+---------------------------+---------------------------+
| 2021-10-31T00:00:00 | 2021-10-31T00:00:00 | 2021-10-30T02:00:00+02:00 | 2021-10-31T02:00:00+02:00 | 2021-10-31T00:00:00+02:00 | 2021-10-31T02:00:00+02:00 |
+---------------------+---------------------+---------------------------+---------------------------+---------------------------+---------------------------+
1 row(s) fetched.
Elapsed 0.031 seconds.

Different number of timestamp casting will result in a different date type in terms of timezone:
- Odd number of timestamp casting --> timestamp without time zone
- Even number of timestamp casting --> timestamp with time zone
- This postgres behavior is mentioned in Better timezone functionalities apache/datafusion#10368 (comment), Support date_bin on timestamps with timezone, properly accounting for Daylight Savings Time apache/datafusion#10602 (comment), and https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-ZONECONVERT

postgres =# SELECT
  pg_typeof('2024-03-31T00:30:00') AS type_unknown,
  pg_typeof('2024-03-31T00:30:00' AT TIME ZONE 'Europe/Brussels') AS type_wo_tz, -- 1 casting
  pg_typeof('2024-03-31T00:30:00' AT TIME ZONE 'UTC' AT TIME ZONE 'Europe/Brussels') AS type_w_tz, -- 2 casting
  pg_typeof('2024-03-31T00:30:00'::timestamp AT TIME ZONE 'UTC') AS type_w_tz, -- 2 casting
  pg_typeof('2024-03-31T00:30:00'::timestamp AT TIME ZONE 'UTC' AT TIME ZONE 'Europe/Brussels') AS type_wo_tz -- 3 casting
;
 type_unknown |         type_wo_tz          |        type_w_tz         |        type_w_tz         |         type_wo_tz
--------------+-----------------------------+--------------------------+--------------------------+-----------------------------
 unknown      | timestamp without time zone | timestamp with time zone | timestamp with time zone | timestamp without time zone
(1 row)

appletreeisyellow · 2024-07-02T19:02:32Z

My proposal is:

For short term fix, use UDF function to_local_time() combined with the current implementation of date_bin() to achieve the desired result
For long term fix, we might need to be clear on the timestamp representation in arrow-rs first before we can moving forward: Propose change timestamp casting with timezone to without timezones (also parsing of timestamps without timezones) apache/arrow-rs#5827

alamb · 2024-07-03T10:30:53Z

My proposal is:

For short term fix, use UDF function to_local_time() combined with the current implementation of date_bin() to achieve the desired result

This makes sense to me

For long term fix, we might need to be clear on the timestamp representation in arrow-rs first before we can moving forward: Propose change timestamp casting with timezone to without timezones (also parsing of timestamps without timezones) apache/arrow-rs#5827

If we made the change proposed in apache/arrow-rs#5827, would that "solve" all these issues?

If so, how would someone implement timezone aware date binning?
Would it be to do like what postgres does and cast back to timestamp without timezone?

appletreeisyellow · 2024-07-04T00:23:04Z

If so, how would someone implement timezone aware date binning?
Would it be to do like what postgres does and cast back to timestamp without timezone?

I'm not sure. I think we need more discussion to define the behavior.

This is how postgres behaves:

If the input timestamp has timezone, date_bin result will be in the timezone of the system, instead of the input timezone! postgres date_bin aware the system timezone, but not the input timezone!
- Currently, DataFusion date_bin returns the same timezone as the input timezone
- Do we want to match the behavior with postgres?
If the input timestamp doesn't have timezone, date_bin result will not have timezone

For example, in postgres:

postgres =# select
  pg_typeof('2021-10-31T01:00:00'::timestamp) as input_datatype,
  date_bin('1 d', '2021-10-31T01:00:00'::timestamp, '1970-01-01T00:00:00'),
  pg_typeof(date_bin('1 d', '2021-10-31T01:00:00'::timestamp, '1970-01-01T00:00:00')) as result_datetype;
       input_datatype        |      date_bin       |       result_datetype
-----------------------------+---------------------+-----------------------------
 timestamp without time zone | 2021-10-31 00:00:00 | timestamp without time zone
(1 row)

postgres=# show timezone;
    TimeZone
-----------------
 America/Chicago
(1 row)

postgres=# select
  pg_typeof('2021-10-31T01:00:00' AT TIME ZONE 'UTC' AT TIME ZONE 'Europe/Brussels') as input_datatype,
  date_bin('1 d', '2021-10-31T01:00:00' AT TIME ZONE 'UTC' AT TIME ZONE 'Europe/Brussels', '1970-01-01T00:00:00'),
  pg_typeof(date_bin('1 d', '2021-10-31T01:00:00' AT TIME ZONE 'UTC' AT TIME ZONE 'Europe/Brussels', '1970-01-01T00:00:00')) as result_datetype;
      input_datatype      |        date_bin        |     result_datetype
--------------------------+------------------------+--------------------------
 timestamp with time zone | 2021-10-30 01:00:00-05 | timestamp with time zone
                                            -- -05 is the system timezone,  it is 01:00 instead of 00:00 because the origin time is '1970-01-01T00:00:00'

# if cast the origin to 'Europe/Brussels' timezone, the result will be at 00:00
postgres=# select date_bin('1 d', '2021-10-31T01:00:00' AT TIME ZONE 'UTC' AT TIME ZONE 'Europe/Brussels', '1970-01-01T00:00:00' AT TIME ZONE 'UTC' AT TIME ZONE 'Europe/Brussels');
        date_bin
------------------------
 2021-10-31 00:00:00-05
(1 row)

postgres=# set time zone 'UTC';
SET

postgres=# show timezone;
 TimeZone
----------
 UTC
(1 row)

postgres=# select
  pg_typeof('2021-10-31T01:00:00' AT TIME ZONE 'UTC' AT TIME ZONE 'Europe/Brussels') as input_datatype,
  date_bin('1 d', '2021-10-31T01:00:00' AT TIME ZONE 'UTC' AT TIME ZONE 'Europe/Brussels', '1970-01-01T00:00:00'),
  pg_typeof(date_bin('1 d', '2021-10-31T01:00:00' AT TIME ZONE 'UTC' AT TIME ZONE 'Europe/Brussels', '1970-01-01T00:00:00')) as result_datetype;
      input_datatype      |        date_bin        |     result_datetype
--------------------------+------------------------+--------------------------
 timestamp with time zone | 2021-10-30 00:00:00+00 | timestamp with time zone
                                            -- -01 is the system timezone, instead of the input timezone
(1 row)

appletreeisyellow · 2024-07-10T19:40:04Z

My proposal is:

For short term fix, use UDF function to_local_time() combined with the current implementation of date_bin() to achieve the desired result

For long term fix, we might need to be clear on the timestamp representation in arrow-rs first before we can moving forward: Propose change timestamp casting with timezone to without timezones (also parsing of timestamps without timezones) apache/arrow-rs#5827

Here is the short term fix PR: apache#11347

For long term fix, there are still discussions going on in apache/arrow-rs#5827 which is the first step of the long term fix.

At this point, I conclude the POC is done, so closing this draft PR.

appletreeisyellow added 2 commits June 6, 2024 11:02

feat: add to_local_time scalar function

a028ee6

chore: wire up the to_local_time as a UDF func

d642b49

appletreeisyellow force-pushed the chunchun/prototype-date_bin-with-timezone branch from cb0e2c4 to d642b49 Compare June 6, 2024 16:07

appletreeisyellow added 2 commits June 6, 2024 16:57

chore: make to_local_time adjust underlying time value

1e8bc26

chore: lint

ce4e560

appletreeisyellow force-pushed the chunchun/prototype-date_bin-with-timezone branch from 34cba19 to ce4e560 Compare June 7, 2024 13:43

appletreeisyellow added 4 commits June 21, 2024 15:27

Merge branch 'main' into chunchun/prototype-date_bin-with-timezone

c3f54ac

Merge branch 'main' into chunchun/prototype-date_bin-with-timezone

1574e41

feat: support date_bin with timezone display

54f1bdc

chore: update tests

a121196

appletreeisyellow commented Jul 2, 2024

View reviewed changes

appletreeisyellow closed this Jul 10, 2024

appletreeisyellow deleted the chunchun/prototype-date_bin-with-timezone branch July 10, 2024 19:34

Omega359 mentioned this pull request Jul 31, 2024

Propose change timestamp casting with timezone to without timezones (also parsing of timestamps without timezones) apache/arrow-rs#5827

Open

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

POC Support `date_bin` on timestamps with timezone #9

POC Support `date_bin` on timestamps with timezone #9

appletreeisyellow commented May 28, 2024 •

edited

Loading

appletreeisyellow Jul 2, 2024

appletreeisyellow commented Jul 2, 2024

appletreeisyellow commented Jul 2, 2024 •

edited

Loading

alamb commented Jul 3, 2024

appletreeisyellow commented Jul 4, 2024 •

edited

Loading

appletreeisyellow commented Jul 10, 2024

POC Support date_bin on timestamps with timezone #9

POC Support date_bin on timestamps with timezone #9

Conversation

appletreeisyellow commented May 28, 2024 • edited Loading

appletreeisyellow Jul 2, 2024

Choose a reason for hiding this comment

appletreeisyellow commented Jul 2, 2024

appletreeisyellow commented Jul 2, 2024 • edited Loading

alamb commented Jul 3, 2024

appletreeisyellow commented Jul 4, 2024 • edited Loading

appletreeisyellow commented Jul 10, 2024

POC Support `date_bin` on timestamps with timezone #9

POC Support `date_bin` on timestamps with timezone #9

appletreeisyellow commented May 28, 2024 •

edited

Loading

appletreeisyellow commented Jul 2, 2024 •

edited

Loading

appletreeisyellow commented Jul 4, 2024 •

edited

Loading