Document that `date_bin` with an explicit origin drifts an hour across a DST transition

[adriangb]

Describe the bug

Passing date_bin an explicit origin chosen to align bins to local midnight works until the timezone's offset changes, then silently shifts by an hour. There is no error and no warning.

This is not a DataFusion bug. PostgreSQL 15.19 and DuckDB 1.5.2 return identical results (see below). All three engines bin on the instant from a fixed origin, so the drift is inherent. What is missing is documentation: the pattern looks correct in every test that does not cross a DST transition.

CREATE OR REPLACE VIEW v AS SELECT arrow_cast(c, 'Timestamp(Second, Some("America/Denver"))') AS t
FROM (VALUES (TIMESTAMP '2024-03-09 06:30:00'), (TIMESTAMP '2024-03-11 06:30:00')) s(c);

SELECT t,
       date_bin(INTERVAL '1 day', t) AS no_origin,
       date_bin(INTERVAL '1 day', t,
                arrow_cast(TIMESTAMP '2024-03-01 07:00:00','Timestamp(Second, Some("UTC"))')) AS with_origin
FROM v;

+---------------------------+---------------------------+---------------------------+
| t                         | no_origin                 | with_origin               |
+---------------------------+---------------------------+---------------------------+
| 2024-03-09T06:30:00-07:00 | 2024-03-08T17:00:00-07:00 | 2024-03-09T00:00:00-07:00 |
| 2024-03-11T06:30:00-06:00 | 2024-03-10T18:00:00-06:00 | 2024-03-11T01:00:00-06:00 |
+---------------------------+---------------------------+---------------------------+

The origin 2024-03-01 07:00:00Z is 2024-03-01 00:00 MST, so bins land on local midnight — until America/Denver springs forward on 2024-03-10. After that the same expression returns 01:00:00-06:00.

To Reproduce

The query above, on DataFusion 55.0.0 (da89c7c85b).

Other engines

Measured with TimeZone = 'America/Denver' for the same instants and origin:

-- PostgreSQL 15.19
SELECT t, date_bin('1 day', t, '2024-03-01 07:00:00+00'::timestamptz), date_trunc('day', t)
FROM (VALUES ('2024-03-09 13:30:00+00'::timestamptz), ('2024-03-11 12:30:00+00'::timestamptz)) v(t);

-- DuckDB 1.5.2 has no date_bin; time_bucket with an origin is the equivalent
SELECT t, time_bucket(INTERVAL '1 day', t, TIMESTAMPTZ '2024-03-01 07:00:00+00'), date_trunc('day', t)
FROM (VALUES (TIMESTAMPTZ '2024-03-09 13:30:00+00'), (TIMESTAMPTZ '2024-03-11 12:30:00+00')) v(t);

t	with origin	date_trunc('day', t)
2024-03-09 06:30:00-07	2024-03-09 00:00:00-07	2024-03-09 00:00:00-07
2024-03-11 06:30:00-06	2024-03-11 01:00:00-06	2024-03-11 00:00:00-06

Both match DataFusion's output exactly.

Expected behavior

Document it on date_bin: an origin fixes an instant, so it does not keep bins on local midnight across a DST transition. Point users at date_trunc for calendar units, and at date_bin(<stride>, to_local_time(t AT TIME ZONE '<zone>')) for strides that are not calendar units. Changing the behaviour would move DataFusion away from PostgreSQL and DuckDB.

Related: #10602 (local-calendar binning) and #25167 (date_bin and date_trunc disagree on timezone-aware input).

[adriangb]

Folded into the issue description: PostgreSQL and DuckDB behave identically, so this is now a documentation issue rather than a bug.