Repository navigation
Grouping by utc timestamp turned into timezoned timestamp still uses utc for group #13962
Description
Describe the bug
For a query like this
SELECT
date_trunc('${timeCoersion}', (timestamp_utc AT TIME ZONE '${timezone}')::timestamptz) AS ts_date,
session_id,
COUNT(*) AS pageviews,
CASE WHEN COUNT(*) = 1 THEN 1 ELSE 0 END AS is_bounce,
MAX(timestamp_utc) AS latest_hit_aux,
MIN(timestamp_utc) AS first_hit_aux
FROM ${"_" + token.replace(/-/g, "_")}
WHERE
${processFilters(filters, useTelemetry)}
timestamp_utc >= TO_TIMESTAMP(${start})
AND timestamp_utc <= TO_TIMESTAMP(${end})
GROUP BY
ts_date,
session_id
I would expect the group by to group by the timezone, but instead it uses utc to group by and only formats the timestamp as the timezone.
To Reproduce
Run a similar query and notice the group by is incorrect
Expected behavior
Group by using the actual timezone
Additional context
No response
Activity
adriangb commentedon Sep 11, 2026
I can explain this, and there is a workaround today.
The intent of the query is right. The wrong result comes from two DataFusion behaviours stacking, not from
date_trunc. I reproduced it on DataFusion 55.0.0 with the column types set explicitly, using three rows at2024-01-01T02:00Z,2024-01-01T23:00Zand2024-01-02T02:00Z. In New York those are 2023-12-31 21:00, 2024-01-01 18:00 and 2024-01-01 21:00, so the correct New York days are2023-12-31 → 1and2024-01-01 → 2.If
timestamp_utcis timezone-aware (Timestamp(_, Some("UTC")))2024-01-01 → 2,2024-01-02 → 1(UTC days)2023-12-31 → 1,2024-01-01 → 2::timestamptz2023-12-31 → 1,2024-01-01 → 22023-12-31 → 1,2024-01-01 → 2Two behaviours combine:
AT TIME ZONEon a timezone-aware value keeps it timezone-aware and only relabels the display zone. PostgreSQL instead returns a naive timestamp holding the New York wall clock. That is Casting existing timestamp to timestamp again strips timezone information #12218.::timestamptzthen replaces the New York label. Withdatafusion.execution.time_zoneunset (the default), it produces a timezone-naive value holding the UTC wall clock. With the session zone set toUTC, it relabels to UTC instead. Either waydate_truncthen sees UTC days. That is TIMESTAMP WITH TIME ZONE can resolve to a timezone-naive type, and casting to it discards an existing timezone #25166.date_truncitself behaves correctly here. It truncates in the value's own zone, which is why removing::timestamptzfixes the result.Workaround today: drop the
::timestamptz.If
timestamp_utcis naive and holds UTC wall clocks (Timestamp(_, None))Then a single
AT TIME ZONEis the wrong conversion in either engine. It reads the stored UTC wall clock as if it were New York local time. Convert from UTC first:That gives
2023-12-31 → 1,2024-01-01 → 2in both DataFusion 55.0.0 and PostgreSQL 17.Fix
#25165 makes
AT TIME ZONEon a timezone-aware value return the naive New York wall clock, as PostgreSQL does. With it applied, the query exactly as reported returns the correct New York days for a timezone-aware column, including with the session zone set toUTC. #25165 is still under review and has a known limitation withCASEinputs. The::timestamptzbehaviour itself is tracked in #25166.