Skip to content

Commit 5b8fcf1

Browse files
adriangbclaude
andauthored
test: characterization suite for timestamps with time zone (apache#25175)
## Which issue does this PR close? None. This PR adds tests only. It records current behaviour, bugs included, for these issues: - apache#12218: `::timestamp` on a tz-aware value gives the UTC wall clock, and `tstz AT TIME ZONE` stays tz-aware - apache#12892: `from_unixtime` and the session time zone (fixed by apache#25161, pinned as fixed) - apache#13212: a tz-naive side of a comparison is read in the other operand's zone, not the session zone - apache#25084: casting a tz-naive value into a named zone errors at DST boundaries - apache#25095: unwrap_cast rewrites a tz-naive column compared with a tz-aware literal under a non-UTC session zone - apache#25166: `TIMESTAMP WITH TIME ZONE` / `::timestamptz` can resolve to a tz-naive type, and a cast to `::timestamptz` replaces an existing zone - apache#25167: `date_bin` and `date_trunc` disagree on tz-aware input - apache#25168: `date_bin` with an explicit origin drifts across a DST transition (PostgreSQL and DuckDB behave the same way) - apache#25170: `AT TIME ZONE '+05:30'` uses the opposite sign convention from PostgreSQL - apache#10368: `date_part('timezone_hour' | 'timezone_minute')` (implemented by apache#25163) ## Rationale for this change Timezone bugs keep coming back in DataFusion because the result depends on several things at once: the session time zone (set or unset), the value's own zone, DST, and optimizer rewrites such as unwrap_cast and constant folding. Few tests cover these combinations. Outside this file, the sqllogictest suite has 47 `SET datafusion.execution.time_zone` statements across 10 files. The PostgreSQL-compatibility tests had no timezone coverage until apache#25164. This file records what DataFusion does today for timestamps with a time zone, so any change to those semantics shows up as a diff in review instead of shipping silently. Where the recorded behaviour is wrong, or disagrees with PostgreSQL, a comment says so and links the issue. When a fix lands, the expected output changes and the comment goes away. apache#25164 is the companion PR. It checks against a real PostgreSQL the cases where the two engines agree. This file covers the cases where they don't. ## What changes are included in this PR? One new file (1674 lines), `datafusion/sqllogictest/test_files/datetime/timestamps_timezone.slt`, in 18 sections: | Section | What it pins | |---|---| | 0–4 | Type and value of `::timestamptz`, `TIMESTAMP [WITH TIME ZONE]` and table columns with the session zone unset, `+00:00`, `+05:30`, `America/Denver`, and `Europe/Brussels` | | 5 | `AT TIME ZONE` on tz-naive and tz-aware values, the composed `(tstz AT TIME ZONE z)::timestamp` idiom, and the `'+05:30'` sign convention | | 6 | Casts in every direction (naive/named/fixed offset), including `::timestamptz` on an already-zoned value | | 7 | Naive → aware → naive round trips, and the reverse | | 8 | Comparisons between tz-aware and tz-naive values, including the unwrap_cast case, with EXPLAIN of the rewritten filters | | 9 | `date_bin`, `date_trunc`, `date_part` (including `timezone*` fields) and `extract` on tz-aware input, including the explicit-origin DST case | | 10 | `to_char`, `from_unixtime`, `to_unixtime`, `to_timestamp*`, `to_local_time` | | 11 | `now`, `current_date`, `current_time`, `make_date` under a session zone | | 12 | `+`/`-` interval across DST, `'1 day'` vs `'24 hours'` | | 13–15 | Aggregates, GROUP BY, ORDER BY, DISTINCT, joins with mixed zones, and UNION / CASE / COALESCE / `greatest` type coercion | | 16 | Ambiguous and non-existent local times (US and EU transitions) | | 17 | A zone with no DST (America/Phoenix) and a half-hour zone (Asia/Kolkata) | No production code changes. ### Interaction with other open PRs Some fix PRs change expectations in this file. Whichever lands second updates the pins. I'd prefer the fixes land first so they stay small, and this file absorbs the change: - apache#25161 (`from_unixtime` follows the session zone) is already on main. The file pins the fixed behaviour. - apache#25163 (`date_part` timezone fields) is already on main. SECTION 9 pins `timezone`, `timezone_hour` and `timezone_minute` as values. - apache#25165 (`AT TIME ZONE` on a tz-aware value returns naive) changes the two SECTION 5b queries. - apache#25171 (docs) doesn't touch this file. Its wording that `'...Z'::timestamptz` "discards the `Z`" should match this file: the offset is applied, but the result carries no zone. - apache#25173 (`generate_series` precisions) doesn't touch this file. ## What is the testing strategy for this PR? The PR is the test. I generated expected output with sqllogictest `--complete`. Then I read every case and rewrote the ones that weren't testing what they meant to test, and added comments with issue links where the behaviour is wrong or differs from PostgreSQL. The file runs in DataFusion only. The PostgreSQL 15 answers quoted in comments come from separate runs, with the PostgreSQL `TimeZone` given in each comment. DuckDB 1.5.2 answers are quoted where they matter (the `date_bin` origin case, and the `'+05:30'` offset string). DST error expectations match only the stable `Arrow error: ...` tail, so changes to the wrapper error chain (for example apache#24920) don't break them. Run with: ``` cargo test -p datafusion-sqllogictest --test sqllogictests -- datetime/timestamps_timezone ``` ## Are there any user-facing changes? No. Tests only. --------- Co-authored-by: Claude Opus 5 <[email protected]>
1 parent 95bb0a0 commit 5b8fcf1

1 file changed

Lines changed: 1677 additions & 0 deletions

File tree

0 commit comments

Comments
 (0)