`date_bin` and `date_trunc` disagree on timezone-aware timestamps

[adriangb]

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:

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.