appletreeisyellow commented on issue #10602:
URL: https://github.com/apache/datafusion/issues/10602#issuecomment-2140077830
I am able to verified that `to_char()` works in `date_bin()` on timestamp
with timezone and daylight saving time 🎉
```sql
select
arrow_typeof(column1) as arrow_type,
column1,
date_bin(interval '1 day', to_char(column1 AT TIME ZONE 'UTC' AT TIME
ZONE 'Europe/Brussels', '%F %X')) as "date_bin"
from t_timezone;
+------------------------------------------------+---------------------------+---------------------+
| arrow_type | column1
| date_bin |
+------------------------------------------------+---------------------------+---------------------+
| Timestamp(Nanosecond, Some("Europe/Brussels")) | 2024-01-01T00:00:01+01:00
| 2024-01-01T00:00:00 | <-- binned correctly
| Timestamp(Nanosecond, Some("Europe/Brussels")) | 2024-02-01T00:00:01+01:00
| 2024-02-01T00:00:00 |
| Timestamp(Nanosecond, Some("Europe/Brussels")) | 2024-03-01T00:00:01+01:00
| 2024-03-01T00:00:00 |
| Timestamp(Nanosecond, Some("Europe/Brussels")) | 2024-04-01T00:00:01+02:00
| 2024-04-01T00:00:00 | <-- binned correctly on daylight savings time
| Timestamp(Nanosecond, Some("Europe/Brussels")) | 2024-05-01T00:00:01+02:00
| 2024-05-01T00:00:00 |
| Timestamp(Nanosecond, Some("Europe/Brussels")) | 2024-06-01T00:00:01+02:00
| 2024-06-01T00:00:00 |
| Timestamp(Nanosecond, Some("Europe/Brussels")) | 2024-07-01T00:00:01+02:00
| 2024-07-01T00:00:00 |
| Timestamp(Nanosecond, Some("Europe/Brussels")) | 2024-08-01T00:00:01+02:00
| 2024-08-01T00:00:00 |
| Timestamp(Nanosecond, Some("Europe/Brussels")) | 2024-09-01T00:00:01+02:00
| 2024-09-01T00:00:00 |
| Timestamp(Nanosecond, Some("Europe/Brussels")) | 2024-10-01T00:00:01+02:00
| 2024-10-01T00:00:00 |
| Timestamp(Nanosecond, Some("Europe/Brussels")) | 2024-11-01T00:00:01+01:00
| 2024-11-01T00:00:00 |
| Timestamp(Nanosecond, Some("Europe/Brussels")) | 2024-12-01T00:00:01+01:00
| 2024-12-01T00:00:00 |
+------------------------------------------------+---------------------------+---------------------+
12 row(s) fetched.
Elapsed 0.024 seconds.
```
```sql
select
column1 as "time in UTC",
column1 AT TIME ZONE 'UTC' AT TIME ZONE 'Europe/Brussels' as "time in
Brussels",
date_bin(interval '1 day', to_char(column1 AT TIME ZONE 'UTC' AT TIME ZONE
'Europe/Brussels', '%F %X')) as "date_bin"
from t_utc;
+----------------------+---------------------------+---------------------+
| time in UTC | time in Brussels | date_bin |
+----------------------+---------------------------+---------------------+
| 2024-04-30T21:30:00Z | 2024-04-30T23:30:00+02:00 | 2024-04-30T00:00:00 |
| 2024-04-30T22:30:00Z | 2024-05-01T00:30:00+02:00 | 2024-05-01T00:00:00 |
<-- binned correctly
| 2024-04-30T23:30:00Z | 2024-05-01T01:30:00+02:00 | 2024-05-01T00:00:00 |
| 2024-05-01T00:00:00Z | 2024-05-01T02:00:00+02:00 | 2024-05-01T00:00:00 |
| 2024-05-01T00:30:00Z | 2024-05-01T02:30:00+02:00 | 2024-05-01T00:00:00 |
| 2024-05-01T10:30:00Z | 2024-05-01T12:30:00+02:00 | 2024-05-01T00:00:00 |
| 2024-05-01T20:30:00Z | 2024-05-01T22:30:00+02:00 | 2024-05-01T00:00:00 |
+----------------------+---------------------------+---------------------+
7 row(s) fetched.
Elapsed 0.023 seconds.
```
<details>
<summary>How to create the data</summary>
<br>
The following code were given by Andrew from
[here](https://github.com/apache/datafusion/issues/10602#issue-2308485729).
Copy here again for easier review.
```sql
-- Dates right around the first of May
create or replace table t AS
VALUES
('2024-04-30T21:30:00'),
('2024-04-30T22:30:00'),
('2024-04-30T23:30:00'),
('2024-05-01T00:00:00'),
('2024-05-01T00:30:00'),
('2024-05-01T10:30:00'),
('2024-05-01T20:30:00')
;
create or replace view t_utc as
select column1::timestamp AT TIME ZONE 'UTC' as "column1"
from t;
```
```sql
create or replace table t AS
VALUES
('2024-01-01T00:00:01'),
('2024-02-01T00:00:01'),
('2024-03-01T00:00:01'),
('2024-04-01T00:00:01'),
('2024-05-01T00:00:01'),
('2024-06-01T00:00:01'),
('2024-07-01T00:00:01'),
('2024-08-01T00:00:01'),
('2024-09-01T00:00:01'),
('2024-10-01T00:00:01'),
('2024-11-01T00:00:01'),
('2024-12-01T00:00:01')
;
create or replace view t_timezone
as
select column1::timestamp AT TIME ZONE 'Europe/Brussels' as "column1"
from t;
```
</details>
--
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: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
