Repository navigation
Commit 33574a1
test: PostgreSQL differential coverage for timestamps with time zone (#25164)
## Which issue does this PR close?
This PR closes no issue. It adds tests only.
Related to these issues. Each one is a divergence from PostgreSQL, so
this PR keeps its queries out of the new file:
- #12218
- #12892
- #13212
- #25084
- #25095
- #25166
- #25170
Companion PR: #25175 pins the
current DataFusion behaviour, bugs included.
## Rationale for this change
Set the session time zone to `America/Denver` in both engines. Then this
query gives the same answer in DataFusion and in PostgreSQL:
```sql
-- ts is 2024-03-09 12:00 MST, one day before the spring-forward transition
SELECT date_part('hour', ts + INTERVAL '1 day')::bigint AS plus_1_day,
date_part('hour', ts + INTERVAL '24 hours')::bigint AS plus_24_hours
FROM tstz_den WHERE id = 1;
```
| Engine | `plus_1_day` | `plus_24_hours` |
| --- | --- | --- |
| DataFusion | `12` | `13` |
| PostgreSQL 15 | `12` | `13` |
`INTERVAL '1 day'` keeps the local wall clock. `INTERVAL '24 hours'`
adds 24 hours of elapsed time, so the local hour moves by one.
**What this PR guarantees.** CI runs each query of the new file on
DataFusion and on PostgreSQL 15. If the answers differ, the build fails.
So a change cannot move DataFusion away from PostgreSQL on these queries
without a red build.
### Why time zone bugs recur
- Time zone rules are hard to check by reading code. Two engines can
each look correct and still give different answers.
- On `main` before this PR, the sqllogictest corpus has 40 `SET
datafusion.execution.time_zone` statements, in 9 files.
- The PostgreSQL differential harness (`test_files/pg_compat/`) has no
time zone coverage. No file in it refers to a time zone or to
`timestamptz`.
- So no test compares the time zone behaviour of DataFusion with a
reference engine. A change can alter an answer, and no test fails.
## What changes are included in this PR?
This PR adds one test file and changes no production code:
`datafusion/sqllogictest/test_files/pg_compat/pg_compat_timestamptz.slt`
(about 700 lines).
### Sections
Each block sets the session time zone in both engines: `SET TimeZone`
for PostgreSQL, and `SET datafusion.execution.time_zone` for DataFusion.
Each block builds its table in the same zone.
| Block | Session time zone | Coverage |
| --- | --- | --- |
| A | `UTC` (DataFusion `+00:00`) | `timestamptz` literals with and
without an offset; `AT TIME ZONE` on naive values; comparison by
instant; `DISTINCT`, `count(DISTINCT)`, `min`, `max`, `GROUP BY`, `ORDER
BY`; filters with aware literals; a self join; `UNION`, `COALESCE`,
`CASE`, `greatest`, `least`; `date_part` and `extract`; `to_timestamp`;
`date_bin` with an explicit origin; interval arithmetic |
| B | `America/Denver` | local `date_part` readings; `'1 day'` against
`'24 hours'` across both DST transitions; the repeated hour at fall
back; `date_trunc` at day, month and hour; `date_bin`; week, month and
year arithmetic; `min`, `max` and `ORDER BY` by instant |
| C | `Asia/Kolkata`, then `America/Phoenix` | a half-hour offset, and a
zone with no DST |
### How we made the expected output
1. We ran the sqllogictest `--complete` mode against a real PostgreSQL
(`PG_COMPAT=true PG_URI=... --complete`). So the answer of PostgreSQL is
the expected output, and DataFusion must match it.
2. We read each result.
3. We removed each query where the two engines disagree. The list is in
"Field research" below.
### Harness constraints
1. **No aware value in a result.** The PostgreSQL runner cannot render
`timestamptz`: `postgres_engine/mod.rs` calls `unimplemented!` for that
type. So each result is a naive `timestamp`, a `bigint`, a `boolean` or
`text`.
2. **Session zone against value zone.** PostgreSQL uses the session time
zone to read a naive literal, to render a `timestamptz`, to apply a day
interval and to run `date_trunc`. DataFusion uses the time zone of the
value. The two agree only when both zones are the same. So each block
sets them to the same zone.
3. **A naive projection must not hide a divergence.** Block A projects
with `::timestamp`. That projection gives the UTC wall clock in both
engines only because the session time zone is UTC. Blocks B and C read
each value through `date_part`. The self-review found one query that
breaks this rule (lines 112-118). A follow-up commit must fix it.
## What is the testing strategy for this PR?
This PR adds tests only. Checks at head `2b0a37fb91`:
- `PG_COMPAT=true PG_URI=... cargo test --profile ci -p
datafusion-sqllogictest --features postgres --test sqllogictests --
pg_compat` against `postgres:15`: 7 of 7 files pass.
- With `log_statement = 'all'` on the server, the PostgreSQL log shows
the harness execute each statement of this file, with zero errors. So
the run really compares the two engines.
- The same command without `PG_COMPAT` (DataFusion only): the file
passes.
- CI on 2026-09-10: all 38 checks pass, the "Run sqllogictest with
Postgres runner" job included.
- On 2026-09-11, #25182 added
`*.slt` to the license header check. The new file now has `#` on header
lines 8 and 10, as `main` expects (fixed in `aafa2e7df4`).
### Field research
#### PostgreSQL version
- CI runs the harness against `postgres:15`: see the
`sqllogictest-postgres` job in `.github/workflows/rust.yml`.
- The harness compares text. If a value renders differently in another
PostgreSQL version, CI fails, even when the instant is correct. So
generate and check the expected output against `postgres:15`, not
against a newer local server.
- An earlier revision of this PR failed in CI on `extract(epoch ...)`.
The expected output had `1719792000`, and CI produced
`1719792000.000000`. The file now casts each `extract` and `date_part`
result to `bigint`.
- We measured that value in `psql` on PostgreSQL 15.19 and 17.11. Both
give `numeric` `1719792000.000000`. So `psql` shows no version
difference for this value, and the cause of the earlier mismatch is not
confirmed.
#### Divergences that this file leaves out
We measured each answer. The DataFusion answers come from the pinned
expectations in #25175, or from
`datafusion-cli` at the same base. PostgreSQL answers come from
`postgres:15`.
| # | Query | Session time zone | DataFusion | PostgreSQL 15 | Issue |
| --- | --- | --- | --- | --- | --- |
| 1 | `arrow_typeof('2024-07-01 12:00:00Z'::timestamptz)` (PostgreSQL:
`pg_typeof`) | DataFusion unset, PostgreSQL `UTC` | `Timestamp(ns)`
(naive) | `timestamp with time zone` |
#25166 |
| 2 | `'2024-07-01 12:00:00'::timestamp::timestamptz::timestamp` |
`America/Denver` | `2024-07-01T18:00:00` | `2024-07-01 12:00:00` |
#12218 |
| 3 | `ts AT TIME ZONE 'Europe/Brussels'`, where `ts` is the aware value
`2024-07-01 12:00:00Z` | DataFusion `+00:00`, PostgreSQL `UTC` |
`Timestamp(ns, "Europe/Brussels")` `2024-07-01T14:00:00+02:00` |
`timestamp without time zone` `2024-07-01 14:00:00` |
#12218 |
| 4 | `TIMESTAMP '2024-07-01 12:00:00' AT TIME ZONE '+05:30'` |
DataFusion unset, PostgreSQL `UTC` | `2024-07-01T12:00:00+05:30` (06:30
UTC) | `2024-07-01 17:30:00+00` |
#25170 |
| 5 | `WHERE ts > '2024-07-01 06:00:00'` on rows at 00:00, 06:00, 12:00
and 18:00 UTC; DataFusion column zone `America/Denver` | DataFusion
unset, PostgreSQL `UTC` | 1 row (18:00 UTC) | 2 rows (12:00 and 18:00
UTC) | closest: #13212 |
| 6 | `WHERE ts = '2024-07-01T06:00:00Z'::timestamptz` on the same rows
| DataFusion unset, PostgreSQL `UTC` | the 12:00 UTC row | the 06:00 UTC
row | #25166 |
| 7 | naive column `= '2024-07-01T18:00:00Z'::timestamptz`, for the
naive row `2024-07-01 12:00:00` | `America/Denver` | 0 rows | 1 row |
#25095 |
| 8 | `date_trunc('day', ts)` for `2024-07-01T00:00:00Z`; DataFusion
column zone `America/Denver` | DataFusion unset, PostgreSQL `UTC` |
`2024-06-30T00:00:00-06:00` | `2024-07-01 00:00:00+00` | no issue |
| 9 | `(TIMESTAMP '2024-01-15 12:00:00' AT TIME ZONE 'America/Denver') =
(TIMESTAMP '2024-07-01 12:00:00' AT TIME ZONE 'America/Denver') -
INTERVAL '168 days'` | DataFusion `+00:00`, PostgreSQL `UTC` | `true` |
`f` | no issue |
| 10 | `'2024-11-03 01:30:00'::timestamptz` (repeated hour) |
`America/Denver` | error: `error computing timezone offset` |
`2024-11-03 01:30:00-07` |
#25084 |
| 11 | `'2024-03-10 02:30:00'::timestamptz` (hour that does not exist) |
`America/Denver` | error: `error computing timezone offset` |
`2024-03-10 03:30:00-06` |
#25084 |
| 12 | `date_part('timezone_hour', ts)` in July | `America/Denver` |
error: `Date part 'timezone_hour' not supported` | `-6` | fix in
#25163 |
| 13 | `date_bin` with a `1 month` stride | DataFusion unset, PostgreSQL
`UTC` | returns a bin | error: `timestamps cannot be binned into
intervals containing months or years` | no issue |
| 14 | `from_unixtime(1719792000)` (PostgreSQL:
`to_timestamp(1719792000)`) | `America/Denver` (PostgreSQL `UTC`) |
`Timestamp(s)` `2024-07-01T00:00:00` (naive) | `timestamp with time
zone` | #12892 |
Rows 5 to 9 have one cause in common: DataFusion uses the time zone of
the value, and PostgreSQL uses the session time zone. That is why each
block of this file sets the two to the same zone.
## Are there any user-facing changes?
No. This PR adds one test file. It changes no production code, no public
API and no behaviour.
🤖 Generated with [Claude Code](https://claude.com/claude-code)
---------
Co-authored-by: Claude Opus 5 <[email protected]>
Co-authored-by: Andrew Lamb <[email protected]>1 parent 87b1f6a commit 33574a1
1 file changed
Lines changed: 704 additions & 0 deletions
0 commit comments