[I] Grouping by utc timestamp turned into timezoned timestamp still uses utc for group [datafusion]

["TheBuilderJR (via GitHub)" <gi...@apache.org>]

TheBuilderJR opened a new issue, #13962:
URL: https://github.com/apache/datafusion/issues/13962

   ### 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_


-- 
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: github-unsubscribe@datafusion.apache.org.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org

Re: [I] Grouping by utc timestamp turned into timezoned timestamp still uses utc for group [datafusion]

["adriangb (via GitHub)" <gi...@apache.org>]

adriangb commented on issue #13962:
URL: https://github.com/apache/datafusion/issues/13962#issuecomment-5636354698

   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"))`)
   
   ```sql
   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 #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 #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`.
   
   ```sql
   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:
   
   ```sql
   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.
   


-- 
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: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org