adriangb opened a new issue, #25167:
URL: https://github.com/apache/datafusion/issues/25167
### Describe the bug
`date_trunc` and `date_bin` give different answers for the same
timezone-aware value and the same unit. `date_trunc` works in the value's own
timezone; `date_bin` works on the UTC instant and then relabels.
For whole-hour zones the difference is visible but each answer is at least a
local midnight. For a zone whose offset is not a whole multiple of the stride
it is worse — `date_bin` returns something that is not a boundary in either
timezone:
```sql
SELECT arrow_cast(TIMESTAMP '2024-01-01 12:00:00','Timestamp(Second,
Some("Asia/Kolkata"))') AS t,
date_trunc('day', ...) AS dtrunc,
date_bin(INTERVAL '1 day', ...) AS dbin;
+---------------------------+---------------------------+---------------------------+
| t | dtrunc | dbin
|
+---------------------------+---------------------------+---------------------------+
| 2024-01-01T12:00:00+05:30 | 2024-01-01T00:00:00+05:30 |
2024-01-01T05:30:00+05:30 |
+---------------------------+---------------------------+---------------------------+
```
`05:30:00+05:30` is UTC midnight rendered in Kolkata. It is not a day
boundary in Kolkata, and as a `GROUP BY` key it is surprising.
America/Denver shows the same disagreement in the more familiar form:
`date_trunc` gives `2024-01-01T00:00:00-07:00`, `date_bin` gives
`2023-12-31T17:00:00-07:00`.
### To Reproduce
The queries above, on DataFusion 55.0.0 (`da89c7c85b`).
### Expected behavior
Not obvious, which is why I am filing it rather than proposing a patch. Each
function individually matches PostgreSQL — PG's `date_trunc(field,
timestamptz)` truncates in the session zone and PG's `date_bin` is
instant-based — so neither is wrong on its own. What is missing is that the
pair is inconsistent, nothing in the codebase or documentation records that
this is intentional, and there is no way for a user to discover it short of
comparing outputs.
At minimum the difference should be a deliberate, documented decision.
Options that seem worth weighing:
- Give `date_bin` an optional timezone argument, or make it timezone-aware
for whole-calendar-unit strides.
- Leave the behaviour and document it prominently on both functions.
Related: #10602 asks for local-calendar binning and is still open for
exactly this reason; the current answer is to compose `date_bin` with
`to_local_time`. #13962 is a different symptom of timezone-sensitive grouping.
Found while adding timezone characterization tests in #25164.
--
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]
