Skip to content

Commit acf2af8

Browse files
adriangbclaude
andauthored
feat: support timezone, timezone_hour and timezone_minute in date_part (apache#25163)
## 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/common.rs‎

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -584,3 +584,15 @@ fn scalar_value(dt: &DataType, r: Option<i64>) -> Result<ScalarValue> {
584584
t => Err(internal_datafusion_err!("Unsupported data type: {t:?}")),
585585
}
586586
}
587+
588+
/// Parse the optional timezone string carried by [`DataType::Timestamp`] into a
589+
/// [`Tz`].
590+
///
591+
/// Returns `Ok(None)` when the timestamp type is timezone-naive.
592+
pub(crate) fn parse_tz(tz: Option<&Arc<str>>) -> Result<Option<Tz>> {
593+
tz.map(|tz| {
594+
tz.parse::<Tz>()
595+
.map_err(|op| exec_datafusion_err!("failed on timezone {tz}: {op:?}"))
596+
})
597+
.transpose()
598+
}

0 commit comments

Comments
 (0)