Repository navigation
Commit acf2af8
## Which issue does this PR close?
- Part of apache#10368. That issue
lists several timezone gaps. This PR covers one bullet: "Extracting the
time offset using `date_part` might be a nice to have". The other
bullets stay open, so this PR does not close the issue.
## Rationale for this change
### What a user sees today
A timezone-aware timestamp carries a UTC offset, but nothing in
DataFusion can read it back:
```sql
> SELECT date_part('timezone', TIMESTAMP '2024-07-01T12:00:00' AT TIME ZONE 'Europe/Brussels') AS utc_offset_seconds;
Execution error: Date part 'timezone' not supported
```
`timezone_hour` and `timezone_minute` fail the same way. There is no
workaround. The offset is not a property of the type either, so a user
cannot look it up once and reuse it: for a named zone it moves with
daylight saving time.
### What a user sees after this PR
```sql
> SELECT date_part('timezone', TIMESTAMP '2024-07-01T12:00:00' AT TIME ZONE 'Europe/Brussels') AS utc_offset_seconds;
+--------------------+
| utc_offset_seconds |
+--------------------+
| 7200 |
+--------------------+
```
`7200` is +02:00, the summer offset of `Europe/Brussels`.
### The new capability, in plain terms
Three new `date_part` fields report the UTC offset of a timezone-aware
timestamp:
| part | meaning | `Europe/Brussels`, July |
| --- | --- | --- |
| `timezone` | the whole UTC offset, in seconds | `7200` |
| `timezone_hour` | whole hours of the offset | `2` |
| `timezone_minute` | whole minutes of the offset, without the hours |
`0` |
Three things follow from the definition:
- The value depends on the instant, not only on the type.
`Europe/Brussels` gives `3600` in January and `7200` in July.
- Offsets that are not whole hours work. `Asia/Kolkata` gives `19800 / 5
/ 30`.
- A negative offset carries its sign on the minute as well as the hour.
`America/St_Johns` in January gives `-12600 / -3 / -30`.
`EXTRACT(TIMEZONE_HOUR FROM ...)` is the alternative syntax for the same
thing.
## What changes are included in this PR?
Two commits.
**1. `refactor: move parse_tz from date_trunc into datetime::common`.**
No behaviour change. `parse_tz` turns the optional timezone string of
`DataType::Timestamp` into an `arrow::array::timezone::Tz`. It was
private to `date_trunc.rs`. It now sits next to the other shared
datetime helpers, so `date_part` reuses it instead of a second parser.
**2. `feat: support timezone, timezone_hour and timezone_minute in
date_part`.** The three fields, with these design points:
- **Per row, not per type.** Each value goes through
`as_datetime_with_timezone::<T>`, and the code reads its offset back.
One pass of `PrimitiveArray::unary_opt` computes the field, so there is
no intermediate offset array. Input nulls survive.
- **Timezone-naive input is an error, not an implicit UTC.** A
`Timestamp(_, None)` carries no offset. The error text is `Date part
'timezone' is not supported for timezone-naive timestamps, got
Timestamp(ns)`. Dates, times, intervals and durations get a similar
error. PostgreSQL rejects these inputs too. DuckDB instead returns `0`,
which this PR treats as the weaker choice: a plain `0` is
indistinguishable from a real UTC value.
- **The return type is `Int32`.** DataFusion already returns `Int32` for
every integral `date_part` field, and the two exceptions are `epoch`
(`Float64`) and `nanosecond` (`Int64`). PostgreSQL returns `double
precision` for all fields, but DataFusion diverges from that today for
`hour`, `minute` and the rest. DuckDB returns `BIGINT`. So an integer
type is both consistent here and precedented elsewhere.
- **`EXTRACT` needs no planner change.** `SQLExpr::Extract` forwards the
`DateTimeField` to `date_part` as its `Display` string. sqlparser
already carries the `Timezone`, `TimezoneHour` and `TimezoneMinute`
variants, so `EXTRACT(TIMEZONE_HOUR FROM ...)` arrives as
`"TIMEZONE_HOUR"` and matches without regard to case.
No existing field changes behaviour. The new parts sit in the `Err(_)`
arm that used to return `Date part '{part}' not supported`.
### Field research
The sign of `timezone_minute` on a negative offset is easy to get
backwards, so I measured it rather than assumed it.
**PostgreSQL 17.11**, in Docker, with `SET TimeZone TO '<zone>'` and a
`timestamptz` literal:
| zone | offset kind | instant | `timezone` | `timezone_hour` |
`timezone_minute` |
| --- | --- | --- | --- | --- | --- |
| `Asia/Kolkata` | positive, 30 minutes | 2024-07-01T12:00:00Z | 19800 |
5 | 30 |
| `Asia/Kathmandu` | positive, 45 minutes | 2024-07-01T12:00:00Z | 20700
| 5 | 45 |
| `America/Denver` | negative, whole hour | 2024-01-01T12:00:00Z |
-25200 | -7 | 0 |
| `America/St_Johns` | negative, 30 minutes | 2024-01-01T12:00:00Z |
**-12600** | **-3** | **-30** |
| `Pacific/Marquesas` | negative, 30 minutes | 2024-07-01T12:00:00Z |
-34200 | -9 | -30 |
| `Europe/Brussels` | DST zone, winter | 2024-01-01T12:00:00Z | 3600 | 1
| 0 |
| `Europe/Brussels` | DST zone, summer | 2024-07-01T12:00:00Z | 7200 | 2
| 0 |
| `Pacific/Chatham` | DST zone, 45 minutes | 2024-01-01T12:00:00Z |
49500 | 13 | 45 |
| `Pacific/Chatham` | DST zone, 45 minutes | 2024-07-01T12:00:00Z |
45900 | 12 | 45 |
| `UTC` | zero | 2024-07-01T12:00:00Z | 0 | 0 | 0 |
So the minute follows the sign of the offset. `Pacific/Chatham` is in
the southern hemisphere, so its January value is the DST one. Also
measured on the same server:
- `NULL` in gives `NULL` out.
- The part name ignores case.
- `tz` is not an accepted spelling.
- `date`, `time` and `interval` input are all rejected.
**DuckDB 1.5.2 supports all three fields.** This is worth stating
plainly, because the opposite would also be a useful finding. DuckDB
agrees with PostgreSQL on every zone and instant in the table above,
sign included. Two differences from PostgreSQL:
- DuckDB returns `BIGINT`, where PostgreSQL returns `double precision`.
- DuckDB returns `0` for a timezone-naive `TIMESTAMP`, where PostgreSQL
raises `unit "timezone" not supported for type timestamp without time
zone`.
DuckDB rejects `DATE` and `INTERVAL` input, as this PR does.
This PR matches both engines on every value, and it follows PostgreSQL
on the timezone-naive error.
## What is the testing strategy for this PR?
**Unit tests** in `datafusion/functions/src/datetime/date_part.rs`, 9
new tests. `timezone_parts_match_postgres` is a table of 15 zone and
instant combinations, asserted for all three fields. It covers every row
of the PostgreSQL table above. The arrays are built by a relabel of UTC
instants, so the cases line up exactly. The other 8 tests cover:
- case insensitivity, including the spelling that `EXTRACT` produces;
- an array across a DST transition, with a null row;
- all four `TimeUnit` variants;
- scalar input;
- `NULL` input;
- the timezone-naive rejection;
- the non-timestamp rejection;
- the `Int32` return field.
**sqllogictest** cases appended to
`datafusion/sqllogictest/test_files/datetime/date_part.slt`:
- fixed offsets `+05:30` and `-03:30`;
- the exact query from the issue;
- named zones in standard time and in daylight saving time;
- the 45-minute `Pacific/Chatham` cases;
- negative offsets that are not whole hours;
- `EXTRACT(...)` syntax;
- `arrow_typeof` of all three results;
- `NULL` input;
- a column across the exact Brussels spring-forward boundary, 01:59:59
against 03:00:00 local;
- four error cases.
Verified locally, on each commit separately:
- commit 1: `cargo check --workspace --all-targets` clean, `cargo clippy
-p datafusion-functions --all-targets -- -D warnings` clean, 344 lib
tests pass.
- commit 2: `cargo clippy --all-targets -- -D warnings` clean across the
workspace, 353 lib tests pass, the full sqllogictest suite passes.
- `cargo fmt --all`, `./ci/scripts/doc_prettier_check.sh` and `typos`
are all clean.
## Are there any user-facing changes?
Yes, and they are additive. Three new `date_part` and `EXTRACT` fields.
No public API changes, and no existing field changes behaviour, so this
PR needs no `api change` label.
`docs/source/user-guide/sql/scalar_functions.md` comes from the
`user_doc!` block through `dev/update_function_docs.sh`. It gains the
three parts, a note on the DST and sign behaviour, the timezone-only
restriction, and a worked example.
One known limit: `datafusion.execution.time_zone` defaults to `None`, so
`now()` is timezone-naive and `date_part('timezone', now())` errors.
`SET datafusion.execution.time_zone` makes it work. That is
apache#25166 and it is out of scope
here.
### Merge order
apache#25175 is a characterization
suite. Its SECTION 9 pins the current rejection:
```
query error DataFusion error: Execution error: Date part 'timezone_hour' not supported
SELECT date_part('timezone_hour', ts) FROM day_denver
```
Once this PR lands, `timezone_hour` and `timezone_minute` return `-6`
and `0` for every row of `day_denver`. So whichever of the two merges
second must update the other, and its CI goes red until it does. That PR
records the same collision in its own description.
🤖 Generated with [Claude Code](https://claude.com/claude-code)
---------
Co-authored-by: Claude Opus 5 <[email protected]>
1 parent 714956b commit acf2af8
5 files changed
Lines changed: 616 additions & 14 deletions
File tree
- datafusion
- functions/src/datetime
- sqllogictest/test_files/datetime
- docs/source/user-guide/sql
| Original file line number | Diff line number | Diff line change | |
|---|---|---|---|
| |||
584 | 584 | | |
585 | 585 | | |
586 | 586 | | |
| 587 | + | |
| 588 | + | |
| 589 | + | |
| 590 | + | |
| 591 | + | |
| 592 | + | |
| 593 | + | |
| 594 | + | |
| 595 | + | |
| 596 | + | |
| 597 | + | |
| 598 | + | |
0 commit comments