adriangb commented on issue #10602: URL: https://github.com/apache/datafusion/issues/10602#issuecomment-5636326224
Two data points for this thread. `to_local_time` (#11401) was merged here as a workaround, and the issue was deliberately left open for native support, so this is not a request to close. **1. The `to_local_time` workaround is correct across DST transitions, including the repeated hour.** The verification earlier in this thread used `to_char`, on a monthly dataset that crosses transitions without landing on them. I checked `to_local_time` itself on DataFusion 55.0.0, grouping UTC instants by local day in `Europe/Brussels`: ```sql SET datafusion.execution.time_zone = 'UTC'; SELECT date_bin(INTERVAL '1 day', to_local_time(column1 AT TIME ZONE 'Europe/Brussels')) AS local_day, count(*) AS n FROM t GROUP BY 1 ORDER BY 1; ``` | dataset | local_day | n | | --- | --- | --- | | the `t_utc` dataset from the issue body | `2024-04-30` | 1 | | | `2024-05-01` | 6 | | spring forward, 2024-03-31 (a 23-hour local day) | `2024-03-30` | 1 | | | `2024-03-31` | 4 | | | `2024-04-01` | 1 | | fall back, 2024-10-27 (a 25-hour local day; 02:00–03:00 happens twice) | `2024-10-27` | 5 | | | `2024-10-28` | 1 | The first rows match the output requested in the issue body. In the fall-back set, both instants that read `02:30` locally (one at `+02:00`, one at `+01:00`) bin to `2024-10-27`. **2. #25165 would make the PostgreSQL spelling work, which addresses the discoverability concern raised here.** @alamb's first comment on this issue notes that in PostgreSQL you get local-day bins by using `AT TIME ZONE` to turn a `timestamptz` back into a `timestamp`, then applying `date_bin`. DataFusion cannot express that today, because `AT TIME ZONE` on a timezone-aware value returns a timezone-aware value (#12218). #25165 changes that. With it applied, ```sql SELECT date_bin(INTERVAL '1 day', column1 AT TIME ZONE 'Europe/Brussels') AS local_day, count(*) AS n FROM t GROUP BY 1 ORDER BY 1; ``` gives byte-identical results to the `to_local_time` spelling above for all three datasets, and the existing `to_local_time` spelling keeps working unchanged. Users could then write the idiom they already know from PostgreSQL instead of having to discover `to_local_time`. That still is not a timezone-aware `date_bin`, which is the remaining ask of this issue, so it should stay open. #25165 is under review and has a known limitation with `CASE` inputs, described at the top of that PR. Related: #25167 (`date_bin` and `date_trunc` disagree on timezone-aware input) and #25168 (aligning `date_bin` to local midnight with an explicit origin drifts by an hour across a DST transition). -- 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]
