Grouping by utc timestamp turned into timezoned timestamp still uses utc for group

[TheBuilderJR]

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

[adriangb]

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 at 2024-01-01T02:00Z, 2024-01-01T23:00Z and 2024-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 are 2023-12-31 → 1 and 2024-01-01 → 2.

If timestamp_utc is timezone-aware (Timestamp(_, Some("UTC")))

date_trunc('day', (timestamp_utc AT TIME ZONE 'America/New_York')::timestamptz)

	DataFusion 55.0.0	PostgreSQL 17
as reported	2024-01-01 → 2, 2024-01-02 → 1 (UTC days)	2023-12-31 → 1, 2024-01-01 → 2
without ::timestamptz	2023-12-31 → 1, 2024-01-01 → 2	2023-12-31 → 1, 2024-01-01 → 2

Two behaviours combine:

1. AT TIME ZONE on 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.
2. ::timestamptz then replaces the New York label. With datafusion.execution.time_zone unset (the default), it produces a timezone-naive value holding the UTC wall clock. With the session zone set to UTC, it relabels to UTC instead. Either way date_trunc then 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_trunc itself behaves correctly here. It truncates in the value's own zone, which is why removing ::timestamptz fixes the result.

Workaround today: drop the ::timestamptz.

date_trunc('day', timestamp_utc AT TIME ZONE 'America/New_York')

If timestamp_utc is naive and holds UTC wall clocks (Timestamp(_, None))

Then a single AT TIME ZONE is 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:

date_trunc('day', timestamp_utc AT TIME ZONE 'UTC' AT TIME ZONE 'America/New_York')

That gives 2023-12-31 → 1, 2024-01-01 → 2 in both DataFusion 55.0.0 and PostgreSQL 17.

Fix

#25165 makes AT TIME ZONE on 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 to UTC. #25165 is still under review and has a known limitation with CASE inputs. The ::timestamptz behaviour itself is tracked in #25166.