appletreeisyellow opened a new pull request, #11347: URL: https://github.com/apache/datafusion/pull/11347
## Which issue does this PR close? Help with https://github.com/apache/datafusion/issues/10602 ## Rationale for this change This PR adds a ScalarUDF function `to_local_time()`: - this function converts a timezone-aware timestamp to local time (with no offset or timezone information). In other words, this function strips off the timezone from the timestamp, while keep the display value of the timestamp the same. See examples below - only accept 1 input with type `Timestamp(..., *)` - returns with type `Timestamp(..., None)` ### Example This is how to use it in `datafusion-cli`: ```sql > 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. ``` ### Example of using `to_local_time()` in `date_bin()` Combine `to_local_time()` with `date_bin()` will look like: ```sql > 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 | +----------------------------------------------------------------------------------------------------+ ``` <details> <summary>Click to see more examples of applying to array values</summary> <br> 1. Write sample data ```sql create or replace table t AS VALUES ('2024-01-01T00:00:01Z'), ('2024-02-01T00:00:01Z'), ('2024-03-01T00:00:01Z'), ('2024-04-01T00:00:01Z'), ('2024-05-01T00:00:01Z'), ('2024-06-01T00:00:01Z'), ('2024-07-01T00:00:01Z'), ('2024-08-01T00:00:01Z'), ('2024-09-01T00:00:01Z'), ('2024-10-01T00:00:01Z'), ('2024-11-01T00:00:01Z'), ('2024-12-01T00:00:01Z') ; create or replace view t_utc as select column1::timestamp AT TIME ZONE 'UTC' as "column1" from t; create or replace view t_timezone as select column1::timestamp AT TIME ZONE 'Europe/Brussels' as "column1" from t; ``` 2. See how tables look like ```sql > select column1, arrow_typeof(column1) from t; +----------------------+-------------------------+ | column1 | arrow_typeof(t.column1) | +----------------------+-------------------------+ | 2024-01-01T00:00:01Z | Utf8 | | 2024-02-01T00:00:01Z | Utf8 | | 2024-03-01T00:00:01Z | Utf8 | | 2024-04-01T00:00:01Z | Utf8 | | 2024-05-01T00:00:01Z | Utf8 | | 2024-06-01T00:00:01Z | Utf8 | | 2024-07-01T00:00:01Z | Utf8 | | 2024-08-01T00:00:01Z | Utf8 | | 2024-09-01T00:00:01Z | Utf8 | | 2024-10-01T00:00:01Z | Utf8 | | 2024-11-01T00:00:01Z | Utf8 | | 2024-12-01T00:00:01Z | Utf8 | +----------------------+-------------------------+ 12 row(s) fetched. Elapsed 0.009 seconds. > select column1, arrow_typeof(column1) from t_utc; +----------------------+------------------------------------+ | column1 | arrow_typeof(t_utc.column1) | +----------------------+------------------------------------+ | 2024-01-01T00:00:01Z | Timestamp(Nanosecond, Some("UTC")) | | 2024-02-01T00:00:01Z | Timestamp(Nanosecond, Some("UTC")) | | 2024-03-01T00:00:01Z | Timestamp(Nanosecond, Some("UTC")) | | 2024-04-01T00:00:01Z | Timestamp(Nanosecond, Some("UTC")) | | 2024-05-01T00:00:01Z | Timestamp(Nanosecond, Some("UTC")) | | 2024-06-01T00:00:01Z | Timestamp(Nanosecond, Some("UTC")) | | 2024-07-01T00:00:01Z | Timestamp(Nanosecond, Some("UTC")) | | 2024-08-01T00:00:01Z | Timestamp(Nanosecond, Some("UTC")) | | 2024-09-01T00:00:01Z | Timestamp(Nanosecond, Some("UTC")) | | 2024-10-01T00:00:01Z | Timestamp(Nanosecond, Some("UTC")) | | 2024-11-01T00:00:01Z | Timestamp(Nanosecond, Some("UTC")) | | 2024-12-01T00:00:01Z | Timestamp(Nanosecond, Some("UTC")) | +----------------------+------------------------------------+ 12 row(s) fetched. Elapsed 0.011 seconds. > select column1, arrow_typeof(column1) from t_timezone; +---------------------------+------------------------------------------------+ | column1 | arrow_typeof(t_timezone.column1) | +---------------------------+------------------------------------------------+ | 2024-01-01T00:00:01+01:00 | Timestamp(Nanosecond, Some("Europe/Brussels")) | | 2024-02-01T00:00:01+01:00 | Timestamp(Nanosecond, Some("Europe/Brussels")) | | 2024-03-01T00:00:01+01:00 | Timestamp(Nanosecond, Some("Europe/Brussels")) | | 2024-04-01T00:00:01+02:00 | Timestamp(Nanosecond, Some("Europe/Brussels")) | | 2024-05-01T00:00:01+02:00 | Timestamp(Nanosecond, Some("Europe/Brussels")) | | 2024-06-01T00:00:01+02:00 | Timestamp(Nanosecond, Some("Europe/Brussels")) | | 2024-07-01T00:00:01+02:00 | Timestamp(Nanosecond, Some("Europe/Brussels")) | | 2024-08-01T00:00:01+02:00 | Timestamp(Nanosecond, Some("Europe/Brussels")) | | 2024-09-01T00:00:01+02:00 | Timestamp(Nanosecond, Some("Europe/Brussels")) | | 2024-10-01T00:00:01+02:00 | Timestamp(Nanosecond, Some("Europe/Brussels")) | | 2024-11-01T00:00:01+01:00 | Timestamp(Nanosecond, Some("Europe/Brussels")) | | 2024-12-01T00:00:01+01:00 | Timestamp(Nanosecond, Some("Europe/Brussels")) | +---------------------------+------------------------------------------------+ 12 row(s) fetched. Elapsed 0.012 seconds. ``` 3. Query using `to_local_time()` ```sql > select column1, to_local_time(column1), arrow_typeof(to_local_time(column1)) from t_utc; +----------------------+------------------------------+--------------------------------------------+ | column1 | to_local_time(t_utc.column1) | arrow_typeof(to_local_time(t_utc.column1)) | +----------------------+------------------------------+--------------------------------------------+ | 2024-01-01T00:00:01Z | 2024-01-01T00:00:01 | Timestamp(Nanosecond, None) | | 2024-02-01T00:00:01Z | 2024-02-01T00:00:01 | Timestamp(Nanosecond, None) | | 2024-03-01T00:00:01Z | 2024-03-01T00:00:01 | Timestamp(Nanosecond, None) | | 2024-04-01T00:00:01Z | 2024-04-01T00:00:01 | Timestamp(Nanosecond, None) | | 2024-05-01T00:00:01Z | 2024-05-01T00:00:01 | Timestamp(Nanosecond, None) | | 2024-06-01T00:00:01Z | 2024-06-01T00:00:01 | Timestamp(Nanosecond, None) | | 2024-07-01T00:00:01Z | 2024-07-01T00:00:01 | Timestamp(Nanosecond, None) | | 2024-08-01T00:00:01Z | 2024-08-01T00:00:01 | Timestamp(Nanosecond, None) | | 2024-09-01T00:00:01Z | 2024-09-01T00:00:01 | Timestamp(Nanosecond, None) | | 2024-10-01T00:00:01Z | 2024-10-01T00:00:01 | Timestamp(Nanosecond, None) | | 2024-11-01T00:00:01Z | 2024-11-01T00:00:01 | Timestamp(Nanosecond, None) | | 2024-12-01T00:00:01Z | 2024-12-01T00:00:01 | Timestamp(Nanosecond, None) | +----------------------+------------------------------+--------------------------------------------+ 12 row(s) fetched. Elapsed 0.015 seconds. > select column1, to_local_time(column1), arrow_typeof(to_local_time(column1)) from t_timezone; +---------------------------+-----------------------------------+-------------------------------------------------+ | column1 | to_local_time(t_timezone.column1) | arrow_typeof(to_local_time(t_timezone.column1)) | +---------------------------+-----------------------------------+-------------------------------------------------+ | 2024-01-01T00:00:01+01:00 | 2024-01-01T00:00:01 | Timestamp(Nanosecond, None) | | 2024-02-01T00:00:01+01:00 | 2024-02-01T00:00:01 | Timestamp(Nanosecond, None) | | 2024-03-01T00:00:01+01:00 | 2024-03-01T00:00:01 | Timestamp(Nanosecond, None) | | 2024-04-01T00:00:01+02:00 | 2024-04-01T00:00:01 | Timestamp(Nanosecond, None) | | 2024-05-01T00:00:01+02:00 | 2024-05-01T00:00:01 | Timestamp(Nanosecond, None) | | 2024-06-01T00:00:01+02:00 | 2024-06-01T00:00:01 | Timestamp(Nanosecond, None) | | 2024-07-01T00:00:01+02:00 | 2024-07-01T00:00:01 | Timestamp(Nanosecond, None) | | 2024-08-01T00:00:01+02:00 | 2024-08-01T00:00:01 | Timestamp(Nanosecond, None) | | 2024-09-01T00:00:01+02:00 | 2024-09-01T00:00:01 | Timestamp(Nanosecond, None) | | 2024-10-01T00:00:01+02:00 | 2024-10-01T00:00:01 | Timestamp(Nanosecond, None) | | 2024-11-01T00:00:01+01:00 | 2024-11-01T00:00:01 | Timestamp(Nanosecond, None) | | 2024-12-01T00:00:01+01:00 | 2024-12-01T00:00:01 | Timestamp(Nanosecond, None) | +---------------------------+-----------------------------------+-------------------------------------------------+ 12 row(s) fetched. Elapsed 0.016 seconds. ``` 4. Combine with `date_bin()` ```sql > select date_bin(interval '1 day', to_local_time(column1)) AT TIME ZONE 'Europe/Brussels' as date_bin from t_utc; +---------------------------+ | date_bin | +---------------------------+ | 2024-01-01T00:00:00+01:00 | | 2024-02-01T00:00:00+01:00 | | 2024-03-01T00:00:00+01:00 | | 2024-04-01T00:00:00+02:00 | | 2024-05-01T00:00:00+02:00 | | 2024-06-01T00:00:00+02:00 | | 2024-07-01T00:00:00+02:00 | | 2024-08-01T00:00:00+02:00 | | 2024-09-01T00:00:00+02:00 | | 2024-10-01T00:00:00+02:00 | | 2024-11-01T00:00:00+01:00 | | 2024-12-01T00:00:00+01:00 | +---------------------------+ 12 row(s) fetched. Elapsed 0.023 seconds. > select date_bin(interval '1 day', to_local_time(column1)) AT TIME ZONE 'Europe/Brussels' as date_bin from t_timezone; +---------------------------+ | date_bin | +---------------------------+ | 2024-01-01T00:00:00+01:00 | | 2024-02-01T00:00:00+01:00 | | 2024-03-01T00:00:00+01:00 | | 2024-04-01T00:00:00+02:00 | | 2024-05-01T00:00:00+02:00 | | 2024-06-01T00:00:00+02:00 | | 2024-07-01T00:00:00+02:00 | | 2024-08-01T00:00:00+02:00 | | 2024-09-01T00:00:00+02:00 | | 2024-10-01T00:00:00+02:00 | | 2024-11-01T00:00:00+01:00 | | 2024-12-01T00:00:00+01:00 | +---------------------------+ 12 row(s) fetched. Elapsed 0.011 seconds. ``` </details> ## What changes are included in this PR? New ScalarUDF function `to_local_time()` with tests ## Are these changes tested? Yes ## Are there any user-facing changes? No API changes. -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: github-unsubscr...@datafusion.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org --------------------------------------------------------------------- To unsubscribe, e-mail: github-unsubscr...@datafusion.apache.org For additional commands, e-mail: github-h...@datafusion.apache.org
