apache / apache/datafusion

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

Open
#13,962 1 comment 0 reactions 0 assignees View on GitHub
bug
Dominant language
Rust
Stars
9.3k
Forks
2.4k
Avg merge
3d 7h
Merged PRs (30d)
344

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_

Contributor guide

Open the contributing guide

Research direction

Start by reproducing the issue with a minimal DataFusion SQL query using date_trunc, AT TIME ZONE, and GROUP BY on the timestamp alias. Trace how the timestamp expression is interpreted for grouping versus display, then add coverage showing that groups use the requested timezone and run the relevant DataFusion test suite.

Written by the indexing model from the issue text.

Assessment

Tech stack
rust, sql
Domain
databases
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Active
Clarity
Needs clarification
Newbie friendliness
45/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.