You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@datafusion.apache.org by gi...@apache.org on 2026/09/22 16:41:29 UTC
(datafusion) 01/01: feat: support `timezone`, `timezone_hour` and `timezone_minute` in `date_part` (#25163)
This is an automated email from the ASF dual-hosted git repository.
github-merge-queue[bot] pushed a commit to branch gh-readonly-queue/main/pr-25163-6e60c20ecef9b76e8db2097442db4d96ba00e6ca
in repository https://gitbox.apache.org/repos/asf/datafusion.git
commit 7caf67d854c267008ae25510145f97f4c6172459
Author: Adrian Garcia Badaracco <17...@users.noreply.github.com>
AuthorDate: Tue Sep 22 15:17:57 2026 +0000
feat: support `timezone`, `timezone_hour` and `timezone_minute` in `date_part` (#25163)
## Which issue does this PR close?
- Part of https://github.com/apache/datafusion/issues/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
https://github.com/apache/datafusion/issues/25166 and it is out of scope
here.
### Merge order
https://github.com/apache/datafusion/pull/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 <no...@anthropic.com>
---
datafusion/functions/src/datetime/common.rs | 12 +
datafusion/functions/src/datetime/date_part.rs | 434 ++++++++++++++++++++-
datafusion/functions/src/datetime/date_trunc.rs | 9 +-
.../sqllogictest/test_files/datetime/date_part.slt | 164 ++++++++
docs/source/user-guide/sql/scalar_functions.md | 11 +
5 files changed, 616 insertions(+), 14 deletions(-)
diff --git a/datafusion/functions/src/datetime/common.rs b/datafusion/functions/src/datetime/common.rs
index 118b6b371b..e1ceab2f3b 100644
--- a/datafusion/functions/src/datetime/common.rs
+++ b/datafusion/functions/src/datetime/common.rs
@@ -584,3 +584,15 @@ fn scalar_value(dt: &DataType, r: Option<i64>) -> Result<ScalarValue> {
t => Err(internal_datafusion_err!("Unsupported data type: {t:?}")),
}
}
+
+/// Parse the optional timezone string carried by [`DataType::Timestamp`] into a
+/// [`Tz`].
+///
+/// Returns `Ok(None)` when the timestamp type is timezone-naive.
+pub(crate) fn parse_tz(tz: Option<&Arc<str>>) -> Result<Option<Tz>> {
+ tz.map(|tz| {
+ tz.parse::<Tz>()
+ .map_err(|op| exec_datafusion_err!("failed on timezone {tz}: {op:?}"))
+ })
+ .transpose()
+}
diff --git a/datafusion/functions/src/datetime/date_part.rs b/datafusion/functions/src/datetime/date_part.rs
index c7f7ef88c0..20f62196eb 100644
--- a/datafusion/functions/src/datetime/date_part.rs
+++ b/datafusion/functions/src/datetime/date_part.rs
@@ -19,6 +19,7 @@ use std::iter::repeat_n;
use std::str::FromStr;
use std::sync::Arc;
+use arrow::array::temporal_conversions::as_datetime_with_timezone;
use arrow::array::timezone::Tz;
use arrow::array::{Array, ArrayRef, Float64Array, Int32Array, Int64Array};
use arrow::compute::{DatePart, binary, date_part};
@@ -31,17 +32,19 @@ use arrow::datatypes::{
IntervalUnit as ArrowIntervalUnit, TimeUnit, TimestampMicrosecondType,
TimestampMillisecondType, TimestampNanosecondType, TimestampSecondType,
};
-use chrono::{Datelike, NaiveDate};
+use chrono::{Datelike, NaiveDate, Offset};
use datafusion_common::types::{NativeType, logical_date};
+use crate::datetime::common::parse_tz;
use datafusion_common::{
Result, ScalarValue,
cast::{
as_date32_array, as_date64_array, as_int32_array, as_interval_dt_array,
- as_interval_mdn_array, as_interval_ym_array, as_time32_millisecond_array,
- as_time32_second_array, as_time64_microsecond_array, as_time64_nanosecond_array,
- as_timestamp_microsecond_array, as_timestamp_millisecond_array,
- as_timestamp_nanosecond_array, as_timestamp_second_array,
+ as_interval_mdn_array, as_interval_ym_array, as_primitive_array,
+ as_time32_millisecond_array, as_time32_second_array, as_time64_microsecond_array,
+ as_time64_nanosecond_array, as_timestamp_microsecond_array,
+ as_timestamp_millisecond_array, as_timestamp_nanosecond_array,
+ as_timestamp_second_array,
},
exec_err, internal_err, not_impl_err,
types::logical_string,
@@ -81,6 +84,11 @@ use datafusion_macros::user_doc;
- doy (day of the year)
- epoch (seconds since Unix epoch for timestamps/dates, total seconds for intervals)
- isodow (ISO 8601 day of the week where Monday is 1 and Sunday is 7)
+ - timezone (UTC offset in seconds)
+ - timezone_hour (whole hours of the UTC offset)
+ - timezone_minute (whole minutes of the UTC offset, excluding the hours)
+
+ The `timezone`, `timezone_hour` and `timezone_minute` parts are only defined for timestamps that carry a timezone; extracting them from a timezone-naive timestamp, a date, a time or an interval is an error. They report the offset that applies at that instant, so they follow daylight saving time: `Europe/Brussels` yields `3600` in January and `7200` in July. For a negative offset each non-zero part carries the sign, so `America/St_Johns` in January yields `-3` hours and `-30` minutes. [...]
"#
),
argument(
@@ -100,6 +108,12 @@ use datafusion_macros::user_doc;
+----------------------------------------------------+
| 1 |
+----------------------------------------------------+
+> SELECT date_part('timezone', TIMESTAMP '2024-07-01T12:00:00' AT TIME ZONE 'Europe/Brussels') AS utc_offset_seconds;
++--------------------+
+| utc_offset_seconds |
++--------------------+
+| 7200 |
++--------------------+
```"#
)]
#[derive(Debug, PartialEq, Eq, Hash)]
@@ -222,7 +236,13 @@ impl ScalarUDFImpl for DatePartFunc {
Ok(DatePart::Nanosecond) => seconds_ns(array.as_ref())?,
Ok(part) => date_part(array.as_ref(), part)?,
Err(_) if is_epoch(part_trim) => epoch(array.as_ref())?,
- Err(_) => return exec_err!("Date part '{part}' not supported"),
+ // `timezone`, `timezone_hour` and `timezone_minute` have no
+ // `DatePart` equivalent, so they are resolved once `DatePart::from_str`
+ // has failed. `TimezonePart::parse` matches lowercase spellings only.
+ Err(_) => match TimezonePart::parse(&part_trim.to_lowercase()) {
+ Some(tz_part) => timezone_part(array.as_ref(), tz_part)?,
+ None => return exec_err!("Date part '{part}' not supported"),
+ },
};
Ok(if is_scalar {
@@ -317,6 +337,100 @@ fn is_nanosecond(part: &str) -> bool {
.unwrap_or(false)
}
+/// The `timezone`, `timezone_hour` and `timezone_minute` fields, which report
+/// the UTC offset that applies to a timezone-aware timestamp *at that instant*.
+///
+/// Because the offset of a named timezone changes with daylight saving time,
+/// these are per-row values, not a property of the type alone.
+#[derive(Debug, Clone, Copy, PartialEq, Eq)]
+enum TimezonePart {
+ /// `timezone`: the whole UTC offset, in seconds
+ Offset,
+ /// `timezone_hour`: whole hours of the UTC offset
+ Hour,
+ /// `timezone_minute`: whole minutes of the UTC offset, excluding the hours.
+ /// The sign follows the sign of the offset (PostgreSQL behaviour), so
+ /// `-03:30` yields `-3` hours and `-30` minutes.
+ Minute,
+}
+
+impl TimezonePart {
+ fn parse(part: &str) -> Option<Self> {
+ match part {
+ "timezone" => Some(Self::Offset),
+ "timezone_hour" => Some(Self::Hour),
+ "timezone_minute" => Some(Self::Minute),
+ _ => None,
+ }
+ }
+
+ fn name(&self) -> &'static str {
+ match self {
+ Self::Offset => "timezone",
+ Self::Hour => "timezone_hour",
+ Self::Minute => "timezone_minute",
+ }
+ }
+
+ /// Extract this field from a UTC offset expressed in seconds.
+ ///
+ /// Rust integer division truncates towards zero, which is what makes the
+ /// sign of `timezone_minute` follow the sign of the offset, matching
+ /// PostgreSQL.
+ fn project(&self, offset_seconds: i32) -> i32 {
+ match self {
+ Self::Offset => offset_seconds,
+ Self::Hour => offset_seconds / 3_600,
+ Self::Minute => (offset_seconds % 3_600) / 60,
+ }
+ }
+}
+
+/// Compute a [`TimezonePart`] for every element of a timezone-aware timestamp
+/// array.
+///
+/// Errors for any other input type, including timezone-naive timestamps, which
+/// carry no offset at all. This matches PostgreSQL, which rejects
+/// `date_part('timezone', <timestamp without time zone>)`.
+fn timezone_part(array: &dyn Array, part: TimezonePart) -> Result<ArrayRef> {
+ let Timestamp(unit, tz_opt) = array.data_type() else {
+ return exec_err!(
+ "Date part '{}' is only supported for timestamps with a timezone, got {}",
+ part.name(),
+ array.data_type()
+ );
+ };
+ let Some(tz) = parse_tz(tz_opt.as_ref())? else {
+ return exec_err!(
+ "Date part '{}' is not supported for timezone-naive timestamps, got {}",
+ part.name(),
+ array.data_type()
+ );
+ };
+
+ match unit {
+ Second => timezone_part_typed::<TimestampSecondType>(array, tz, part),
+ Millisecond => timezone_part_typed::<TimestampMillisecondType>(array, tz, part),
+ Microsecond => timezone_part_typed::<TimestampMicrosecondType>(array, tz, part),
+ Nanosecond => timezone_part_typed::<TimestampNanosecondType>(array, tz, part),
+ }
+}
+
+fn timezone_part_typed<T: ArrowTimestampType>(
+ array: &dyn Array,
+ tz: Tz,
+ part: TimezonePart,
+) -> Result<ArrayRef> {
+ let array = as_primitive_array::<T>(array)?;
+ // `unary_opt` keeps the input nulls and additionally nulls out any value
+ // that cannot be represented as a `DateTime` (out of chrono's range).
+ let result: Int32Array = array.unary_opt(|value| {
+ as_datetime_with_timezone::<T>(value, tz)
+ .map(|dt| part.project(dt.offset().fix().local_minus_utc()))
+ });
+ Ok(Arc::new(result))
+}
+
fn date_to_scalar(date: NaiveDate, target_type: &DataType) -> Option<ScalarValue> {
Some(match target_type {
Date32 => ScalarValue::Date32(Some(Date32Type::from_naive_date(date))),
@@ -572,3 +686,311 @@ fn seconds_ns(array: &dyn Array) -> Result<ArrayRef> {
Ok(Arc::new(r))
}
}
+
+#[cfg(test)]
+mod tests {
+ use std::sync::Arc;
+
+ use super::DatePartFunc;
+ use arrow::array::{
+ Array, Int32Array, TimestampMicrosecondArray, TimestampMillisecondArray,
+ TimestampNanosecondArray, TimestampSecondArray,
+ };
+ use arrow::compute::kernels::cast_utils::string_to_timestamp_nanos;
+ use arrow::datatypes::{DataType, Field, FieldRef, TimeUnit};
+ use datafusion_common::ScalarValue;
+ use datafusion_common::cast::as_int32_array;
+ use datafusion_common::config::ConfigOptions;
+ use datafusion_expr::{
+ ColumnarValue, ReturnFieldArgs, ScalarFunctionArgs, ScalarUDFImpl,
+ };
+
+ /// Invoke `date_part(part, value)` with a single (already typed) argument.
+ fn invoke_date_part(
+ part: &str,
+ value: ColumnarValue,
+ number_rows: usize,
+ ) -> datafusion_common::Result<ColumnarValue> {
+ let value_field: FieldRef = Field::new("b", value.data_type(), true).into();
+ let args = ScalarFunctionArgs {
+ args: vec![ColumnarValue::Scalar(ScalarValue::from(part)), value],
+ arg_fields: vec![Field::new("a", DataType::Utf8, false).into(), value_field],
+ number_rows,
+ return_field: Field::new("f", DataType::Int32, true).into(),
+ config_options: Arc::new(ConfigOptions::default()),
+ };
+ DatePartFunc::new().invoke_with_args(args)
+ }
+
+ fn ts_nanos(s: &str) -> i64 {
+ string_to_timestamp_nanos(s).unwrap()
+ }
+
+ /// `date_part(part, <ts with tz>)` over a single-element array.
+ fn tz_part(part: &str, tz: &str, utc_instant: &str) -> Option<i32> {
+ let array = TimestampNanosecondArray::from(vec![Some(ts_nanos(utc_instant))])
+ .with_timezone(tz);
+ let result = invoke_date_part(part, ColumnarValue::Array(Arc::new(array)), 1)
+ .unwrap()
+ .to_array(1)
+ .unwrap();
+ assert_eq!(result.data_type(), &DataType::Int32);
+ let result = as_int32_array(&result).unwrap();
+ result.is_valid(0).then(|| result.value(0))
+ }
+
+ /// Expected values were cross-checked against PostgreSQL 17:
+ /// `SET TimeZone TO '<tz>'; SELECT date_part('timezone', '<instant>'::timestamptz);`
+ #[test]
+ fn timezone_parts_match_postgres() {
+ // (timezone, UTC instant, offset seconds, offset hours, offset minutes)
+ let cases = [
+ // Fixed offsets carried directly by the Arrow type
+ ("+05:30", "2024-07-01T12:00:00Z", 19_800, 5, 30),
+ ("-03:30", "2024-07-01T12:00:00Z", -12_600, -3, -30),
+ ("+00:00", "2024-07-01T12:00:00Z", 0, 0, 0),
+ ("UTC", "2024-07-01T12:00:00Z", 0, 0, 0),
+ // Named timezone, standard time vs daylight saving time
+ ("Europe/Brussels", "2024-01-01T12:00:00Z", 3_600, 1, 0),
+ ("Europe/Brussels", "2024-07-01T12:00:00Z", 7_200, 2, 0),
+ ("America/Denver", "2024-01-01T12:00:00Z", -25_200, -7, 0),
+ ("America/Denver", "2024-07-01T12:00:00Z", -21_600, -6, 0),
+ // 45 minute offsets (southern hemisphere: January is DST)
+ ("Pacific/Chatham", "2024-01-01T12:00:00Z", 49_500, 13, 45),
+ ("Pacific/Chatham", "2024-07-01T12:00:00Z", 45_900, 12, 45),
+ // Negative offsets keep the sign on both hour and minute
+ ("America/St_Johns", "2024-01-01T12:00:00Z", -12_600, -3, -30),
+ ("America/St_Johns", "2024-07-01T12:00:00Z", -9_000, -2, -30),
+ (
+ "Pacific/Marquesas",
+ "2024-07-01T12:00:00Z",
+ -34_200,
+ -9,
+ -30,
+ ),
+ ("Asia/Kolkata", "2024-07-01T12:00:00Z", 19_800, 5, 30),
+ // A sub-hour offset: the hour part is zero and so cannot show the
+ // sign, while the minute part still carries it. PostgreSQL 17
+ // gives -2588 / 0 / -43 for this instant.
+ ("Africa/Monrovia", "1900-01-01T12:00:00Z", -2_588, 0, -43),
+ ("Asia/Kathmandu", "2024-07-01T12:00:00Z", 20_700, 5, 45),
+ ];
+
+ for (tz, instant, secs, hours, minutes) in cases {
+ assert_eq!(
+ tz_part("timezone", tz, instant),
+ Some(secs),
+ "timezone for {tz} at {instant}"
+ );
+ assert_eq!(
+ tz_part("timezone_hour", tz, instant),
+ Some(hours),
+ "timezone_hour for {tz} at {instant}"
+ );
+ assert_eq!(
+ tz_part("timezone_minute", tz, instant),
+ Some(minutes),
+ "timezone_minute for {tz} at {instant}"
+ );
+ }
+ }
+
+ #[test]
+ fn timezone_parts_are_case_insensitive() {
+ assert_eq!(
+ tz_part("TIMEZONE", "Europe/Brussels", "2024-07-01T12:00:00Z"),
+ Some(7_200)
+ );
+ // The spelling produced by `EXTRACT(TIMEZONE_HOUR FROM ..)`
+ assert_eq!(
+ tz_part("TIMEZONE_HOUR", "Asia/Kolkata", "2024-07-01T12:00:00Z"),
+ Some(5)
+ );
+ assert_eq!(
+ tz_part("Timezone_Minute", "Asia/Kolkata", "2024-07-01T12:00:00Z"),
+ Some(30)
+ );
+ }
+
+ #[test]
+ fn timezone_parts_over_array_with_dst_transition_and_nulls() {
+ // Two instants either side of the Brussels DST switch, plus a null
+ let array = TimestampNanosecondArray::from(vec![
+ Some(ts_nanos("2024-01-15T00:00:00Z")),
+ Some(ts_nanos("2024-07-15T00:00:00Z")),
+ None,
+ Some(ts_nanos("2024-11-15T00:00:00Z")),
+ ])
+ .with_timezone("Europe/Brussels");
+
+ let result = invoke_date_part(
+ "timezone",
+ ColumnarValue::Array(Arc::new(array.clone())),
+ 4,
+ )
+ .unwrap()
+ .to_array(4)
+ .unwrap();
+ assert_eq!(
+ as_int32_array(&result).unwrap(),
+ &Int32Array::from(vec![Some(3_600), Some(7_200), None, Some(3_600)])
+ );
+
+ let result =
+ invoke_date_part("timezone_hour", ColumnarValue::Array(Arc::new(array)), 4)
+ .unwrap()
+ .to_array(4)
+ .unwrap();
+ assert_eq!(
+ as_int32_array(&result).unwrap(),
+ &Int32Array::from(vec![Some(1), Some(2), None, Some(1)])
+ );
+ }
+
+ #[test]
+ fn timezone_parts_for_all_time_units() {
+ // 2024-07-01T12:00:00Z in Kolkata (+05:30) regardless of precision
+ let second =
+ TimestampSecondArray::from(vec![1_719_835_200]).with_timezone("Asia/Kolkata");
+ let milli = TimestampMillisecondArray::from(vec![1_719_835_200_000])
+ .with_timezone("Asia/Kolkata");
+ let micro = TimestampMicrosecondArray::from(vec![1_719_835_200_000_000])
+ .with_timezone("Asia/Kolkata");
+ let nano = TimestampNanosecondArray::from(vec![1_719_835_200_000_000_000])
+ .with_timezone("Asia/Kolkata");
+
+ for array in [
+ Arc::new(second) as Arc<dyn Array>,
+ Arc::new(milli),
+ Arc::new(micro),
+ Arc::new(nano),
+ ] {
+ let dt = array.data_type().clone();
+ let result = invoke_date_part("timezone", ColumnarValue::Array(array), 1)
+ .unwrap()
+ .to_array(1)
+ .unwrap();
+ assert_eq!(
+ as_int32_array(&result).unwrap(),
+ &Int32Array::from(vec![19_800]),
+ "unexpected offset for {dt}"
+ );
+ }
+ }
+
+ #[test]
+ fn timezone_parts_on_scalar_input() {
+ let scalar = ColumnarValue::Scalar(ScalarValue::TimestampNanosecond(
+ Some(ts_nanos("2024-07-01T12:00:00Z")),
+ Some("Europe/Brussels".into()),
+ ));
+ let ColumnarValue::Scalar(result) =
+ invoke_date_part("timezone", scalar, 1).unwrap()
+ else {
+ panic!("expected a scalar result for a scalar input");
+ };
+ assert_eq!(result, ScalarValue::Int32(Some(7_200)));
+ }
+
+ #[test]
+ fn timezone_parts_on_null_input() {
+ for part in ["timezone", "timezone_hour", "timezone_minute"] {
+ let scalar = ColumnarValue::Scalar(ScalarValue::TimestampNanosecond(
+ None,
+ Some("Europe/Brussels".into()),
+ ));
+ let ColumnarValue::Scalar(result) =
+ invoke_date_part(part, scalar, 1).unwrap()
+ else {
+ panic!("expected a scalar result for a scalar input");
+ };
+ assert_eq!(result, ScalarValue::Int32(None));
+ }
+ }
+
+ #[test]
+ fn timezone_parts_reject_timezone_naive_timestamps() {
+ for part in ["timezone", "timezone_hour", "timezone_minute"] {
+ let scalar = ColumnarValue::Scalar(ScalarValue::TimestampNanosecond(
+ Some(ts_nanos("2024-07-01T12:00:00Z")),
+ None,
+ ));
+ let err = invoke_date_part(part, scalar, 1).unwrap_err().to_string();
+ assert!(
+ err.contains(&format!(
+ "Date part '{part}' is not supported for timezone-naive timestamps"
+ )),
+ "unexpected error for {part}: {err}"
+ );
+ }
+ }
+
+ #[test]
+ fn timezone_parts_reject_non_timestamp_input() {
+ let scalar = ColumnarValue::Scalar(ScalarValue::Date32(Some(19_875)));
+ let err = invoke_date_part("timezone", scalar, 1)
+ .unwrap_err()
+ .to_string();
+ assert!(
+ err.contains(
+ "Date part 'timezone' is only supported for timestamps with a timezone"
+ ),
+ "unexpected error: {err}"
+ );
+ }
+
+ /// An Arrow timestamp type can carry any string as its timezone, because
+ /// nothing validates it at construction time. SQL cannot produce such an
+ /// array, but an externally built Arrow schema can, so the invalid value
+ /// must surface as an execution error that names the offending timezone.
+ #[test]
+ fn timezone_parts_reject_invalid_arrow_timezone() {
+ for part in ["timezone", "timezone_hour", "timezone_minute"] {
+ let array = TimestampNanosecondArray::from(vec![Some(ts_nanos(
+ "2024-07-01T12:00:00Z",
+ ))])
+ .with_timezone("Not/A_Timezone");
+
+ let err = invoke_date_part(part, ColumnarValue::Array(Arc::new(array)), 1)
+ .unwrap_err()
+ .to_string();
+
+ assert!(
+ err.contains("Execution error"),
+ "expected an execution error for {part}: {err}"
+ );
+ assert!(
+ err.contains("Not/A_Timezone"),
+ "error for {part} must name the offending timezone: {err}"
+ );
+ assert!(
+ err.contains("failed on timezone"),
+ "error for {part} must keep the parse context: {err}"
+ );
+ }
+ }
+
+ #[test]
+ fn timezone_parts_return_int32() {
+ let arg_fields: Vec<FieldRef> = vec![
+ Field::new("a", DataType::Utf8, false).into(),
+ Field::new(
+ "b",
+ DataType::Timestamp(TimeUnit::Nanosecond, Some("Europe/Brussels".into())),
+ true,
+ )
+ .into(),
+ ];
+ for part in ["timezone", "timezone_hour", "timezone_minute"] {
+ let part = ScalarValue::from(part);
+ let field = DatePartFunc::new()
+ .return_field_from_args(ReturnFieldArgs {
+ arg_fields: &arg_fields,
+ scalar_arguments: &[Some(&part), None],
+ })
+ .unwrap();
+ assert_eq!(field.data_type(), &DataType::Int32);
+ assert!(field.is_nullable());
+ }
+ }
+}
diff --git a/datafusion/functions/src/datetime/date_trunc.rs b/datafusion/functions/src/datetime/date_trunc.rs
index 2c677213a4..65b74050cd 100644
--- a/datafusion/functions/src/datetime/date_trunc.rs
+++ b/datafusion/functions/src/datetime/date_trunc.rs
@@ -20,6 +20,7 @@ use std::ops::{Add, Sub};
use std::str::FromStr;
use std::sync::Arc;
+use crate::datetime::common::parse_tz;
use arrow::array::temporal_conversions::{
MICROSECONDS, MILLISECONDS, NANOSECONDS, as_datetime_with_timezone,
};
@@ -869,14 +870,6 @@ fn general_date_trunc(
Ok(result)
}
-fn parse_tz(tz: Option<&Arc<str>>) -> Result<Option<Tz>> {
- tz.map(|tz| {
- Tz::from_str(tz)
- .map_err(|op| exec_datafusion_err!("failed on timezone {tz}: {op:?}"))
- })
- .transpose()
-}
-
#[cfg(test)]
mod tests {
use std::sync::Arc;
diff --git a/datafusion/sqllogictest/test_files/datetime/date_part.slt b/datafusion/sqllogictest/test_files/datetime/date_part.slt
index 18a81bd94f..03269772d9 100644
--- a/datafusion/sqllogictest/test_files/datetime/date_part.slt
+++ b/datafusion/sqllogictest/test_files/datetime/date_part.slt
@@ -1937,3 +1937,167 @@ SELECT count(*) FROM year_bound_lima WHERE EXTRACT(YEAR FROM ts_nano) = 1993;
statement ok
DROP TABLE year_bound_lima;
+
+##########
+## `timezone`, `timezone_hour` and `timezone_minute`
+##
+## These report the UTC offset that applies to a timezone-aware timestamp at
+## that instant, so they follow daylight saving time. Expected values were
+## cross-checked against PostgreSQL 17 for the corresponding instants.
+##########
+
+# Fixed offsets carried by the Arrow type
+query III
+SELECT
+ date_part('timezone', arrow_cast(TIMESTAMP '2024-07-01T12:00:00', 'Timestamp(Nanosecond, Some("+05:30"))')),
+ date_part('timezone_hour', arrow_cast(TIMESTAMP '2024-07-01T12:00:00', 'Timestamp(Nanosecond, Some("+05:30"))')),
+ date_part('timezone_minute', arrow_cast(TIMESTAMP '2024-07-01T12:00:00', 'Timestamp(Nanosecond, Some("+05:30"))'))
+----
+19800 5 30
+
+# Negative fixed offset: the sign is carried by both the hour and the minute
+query III
+SELECT
+ date_part('timezone', arrow_cast(TIMESTAMP '2024-07-01T12:00:00', 'Timestamp(Nanosecond, Some("-03:30"))')),
+ date_part('timezone_hour', arrow_cast(TIMESTAMP '2024-07-01T12:00:00', 'Timestamp(Nanosecond, Some("-03:30"))')),
+ date_part('timezone_minute', arrow_cast(TIMESTAMP '2024-07-01T12:00:00', 'Timestamp(Nanosecond, Some("-03:30"))'))
+----
+-12600 -3 -30
+
+# The exact query from https://github.com/apache/datafusion/issues/10368
+query I
+SELECT date_part('timezone', '2024-07-01T12:00:00Z'::timestamptz AT TIME ZONE 'Europe/Brussels')
+----
+7200
+
+# Named timezone: standard time in January, daylight saving time in July
+query III
+SELECT
+ date_part('timezone', '2024-01-01T12:00:00Z'::timestamptz AT TIME ZONE 'Europe/Brussels'),
+ date_part('timezone_hour', '2024-01-01T12:00:00Z'::timestamptz AT TIME ZONE 'Europe/Brussels'),
+ date_part('timezone_minute', '2024-01-01T12:00:00Z'::timestamptz AT TIME ZONE 'Europe/Brussels')
+----
+3600 1 0
+
+query III
+SELECT
+ date_part('timezone', '2024-01-01T12:00:00Z'::timestamptz AT TIME ZONE 'America/Denver'),
+ date_part('timezone', '2024-07-01T12:00:00Z'::timestamptz AT TIME ZONE 'America/Denver'),
+ date_part('timezone_hour', '2024-07-01T12:00:00Z'::timestamptz AT TIME ZONE 'America/Denver')
+----
+-25200 -21600 -6
+
+# 45 minute offsets. Chatham is in the southern hemisphere, so January is DST
+query III
+SELECT
+ date_part('timezone', '2024-01-01T12:00:00Z'::timestamptz AT TIME ZONE 'Pacific/Chatham'),
+ date_part('timezone_hour', '2024-01-01T12:00:00Z'::timestamptz AT TIME ZONE 'Pacific/Chatham'),
+ date_part('timezone_minute', '2024-01-01T12:00:00Z'::timestamptz AT TIME ZONE 'Pacific/Chatham')
+----
+49500 13 45
+
+query III
+SELECT
+ date_part('timezone', '2024-07-01T12:00:00Z'::timestamptz AT TIME ZONE 'Pacific/Chatham'),
+ date_part('timezone_hour', '2024-07-01T12:00:00Z'::timestamptz AT TIME ZONE 'Pacific/Chatham'),
+ date_part('timezone_minute', '2024-07-01T12:00:00Z'::timestamptz AT TIME ZONE 'Pacific/Chatham')
+----
+45900 12 45
+
+# Negative non-whole-hour offset, standard time and daylight saving time
+query III
+SELECT
+ date_part('timezone', '2024-01-01T12:00:00Z'::timestamptz AT TIME ZONE 'America/St_Johns'),
+ date_part('timezone_hour', '2024-01-01T12:00:00Z'::timestamptz AT TIME ZONE 'America/St_Johns'),
+ date_part('timezone_minute', '2024-01-01T12:00:00Z'::timestamptz AT TIME ZONE 'America/St_Johns')
+----
+-12600 -3 -30
+
+query III
+SELECT
+ date_part('timezone', '2024-07-01T12:00:00Z'::timestamptz AT TIME ZONE 'America/St_Johns'),
+ date_part('timezone_hour', '2024-07-01T12:00:00Z'::timestamptz AT TIME ZONE 'America/St_Johns'),
+ date_part('timezone_minute', '2024-07-01T12:00:00Z'::timestamptz AT TIME ZONE 'America/St_Johns')
+----
+-9000 -2 -30
+
+# `EXTRACT(field FROM source)` is the alternative syntax for the same function
+query III
+SELECT
+ EXTRACT(TIMEZONE FROM '2024-07-01T12:00:00Z'::timestamptz AT TIME ZONE 'Asia/Kolkata'),
+ EXTRACT(TIMEZONE_HOUR FROM '2024-07-01T12:00:00Z'::timestamptz AT TIME ZONE 'Asia/Kolkata'),
+ EXTRACT(TIMEZONE_MINUTE FROM '2024-07-01T12:00:00Z'::timestamptz AT TIME ZONE 'Asia/Kolkata')
+----
+19800 5 30
+
+# The part name is case insensitive
+query II
+SELECT
+ date_part('TIMEZONE', '2024-07-01T12:00:00Z'::timestamptz AT TIME ZONE 'Asia/Kathmandu'),
+ date_part('Timezone_Minute', '2024-07-01T12:00:00Z'::timestamptz AT TIME ZONE 'Asia/Kathmandu')
+----
+20700 45
+
+# Like every other date_part field except `epoch`, these return Int32
+query TTT
+SELECT
+ arrow_typeof(date_part('timezone', '2024-07-01T12:00:00Z'::timestamptz AT TIME ZONE 'UTC')),
+ arrow_typeof(date_part('timezone_hour', '2024-07-01T12:00:00Z'::timestamptz AT TIME ZONE 'UTC')),
+ arrow_typeof(date_part('timezone_minute', '2024-07-01T12:00:00Z'::timestamptz AT TIME ZONE 'UTC'))
+----
+Int32 Int32 Int32
+
+# NULL in, NULL out
+query III
+SELECT
+ date_part('timezone', arrow_cast(NULL, 'Timestamp(Nanosecond, Some("Europe/Brussels"))')),
+ date_part('timezone_hour', arrow_cast(NULL, 'Timestamp(Nanosecond, Some("Europe/Brussels"))')),
+ date_part('timezone_minute', arrow_cast(NULL, 'Timestamp(Nanosecond, Some("Europe/Brussels"))'))
+----
+NULL NULL NULL
+
+# Column input, straddling the exact daylight saving time transition, with a
+# NULL row. Casting a naive timestamp to a timezone-aware one reads it as a
+# local wall clock time, and Brussels springs forward from 02:00 to 03:00 local
+# on 2024-03-31, so 01:59:59 local is still CET and 03:00:00 local is CEST.
+statement ok
+CREATE TABLE tz_offsets AS
+SELECT arrow_cast(column1, 'Timestamp(Nanosecond, Some("Europe/Brussels"))') AS ts
+FROM (VALUES
+ (TIMESTAMP '2024-01-15T00:00:00'),
+ (TIMESTAMP '2024-03-31T01:59:59'),
+ (TIMESTAMP '2024-03-31T03:00:00'),
+ (TIMESTAMP '2024-07-15T00:00:00'),
+ (TIMESTAMP '2024-11-15T00:00:00'),
+ (CAST(NULL AS TIMESTAMP))
+);
+
+query III
+SELECT
+ date_part('timezone', ts),
+ date_part('timezone_hour', ts),
+ date_part('timezone_minute', ts)
+FROM tz_offsets
+----
+3600 1 0
+3600 1 0
+7200 2 0
+7200 2 0
+3600 1 0
+NULL NULL NULL
+
+statement ok
+DROP TABLE tz_offsets;
+
+# These parts are only defined for timestamps that carry a timezone
+query error DataFusion error: Execution error: Date part 'timezone' is not supported for timezone\-naive timestamps, got Timestamp\(ns\)
+SELECT date_part('timezone', TIMESTAMP '2024-07-01T12:00:00')
+
+query error DataFusion error: Execution error: Date part 'timezone_hour' is not supported for timezone\-naive timestamps, got Timestamp\(ns\)
+SELECT EXTRACT(TIMEZONE_HOUR FROM TIMESTAMP '2024-07-01T12:00:00')
+
+query error DataFusion error: Execution error: Date part 'timezone' is only supported for timestamps with a timezone, got Date32
+SELECT date_part('timezone', DATE '2024-07-01')
+
+query error DataFusion error: Execution error: Date part 'timezone_minute' is only supported for timestamps with a timezone, got Interval\(MonthDayNano\)
+SELECT date_part('timezone_minute', INTERVAL '1' DAY)
diff --git a/docs/source/user-guide/sql/scalar_functions.md b/docs/source/user-guide/sql/scalar_functions.md
index 11f50b84e3..d1a9f37fc9 100644
--- a/docs/source/user-guide/sql/scalar_functions.md
+++ b/docs/source/user-guide/sql/scalar_functions.md
@@ -2563,6 +2563,11 @@ date_part(part, expression)
- doy (day of the year)
- epoch (seconds since Unix epoch for timestamps/dates, total seconds for intervals)
- isodow (ISO 8601 day of the week where Monday is 1 and Sunday is 7)
+ - timezone (UTC offset in seconds)
+ - timezone_hour (whole hours of the UTC offset)
+ - timezone_minute (whole minutes of the UTC offset, excluding the hours)
+
+ The `timezone`, `timezone_hour` and `timezone_minute` parts are only defined for timestamps that carry a timezone; extracting them from a timezone-naive timestamp, a date, a time or an interval is an error. They report the offset that applies at that instant, so they follow daylight saving time: `Europe/Brussels` yields `3600` in January and `7200` in July. For a negative offset each non-zero part carries the sign, so `America/St_Johns` in January yields `-3` hours and `-30` minutes. A [...]
- **expression**: Time expression to operate on. Can be a constant, column, or function.
@@ -2581,6 +2586,12 @@ date_part(part, expression)
+----------------------------------------------------+
| 1 |
+----------------------------------------------------+
+> SELECT date_part('timezone', TIMESTAMP '2024-07-01T12:00:00' AT TIME ZONE 'Europe/Brussels') AS utc_offset_seconds;
++--------------------+
+| utc_offset_seconds |
++--------------------+
+| 7200 |
++--------------------+
```
#### Alternative Syntax
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@datafusion.apache.org
For additional commands, e-mail: commits-help@datafusion.apache.org