Skip to content

Commit 5df8177

Browse files
adriangbclaude
andcommitted
fix: AT TIME ZONE on a tz-aware timestamp returns a naive timestamp
`expr AT TIME ZONE 'tz'` was unconditionally lowered to `CAST(expr AS Timestamp(Nanosecond, Some(tz)))`. That is right for a timezone-naive input -- arrow's `Timestamp(_, None) -> Timestamp(_, Some(tz))` cast reads the naive value as local time in `tz` -- but wrong for a timezone-aware one, where it merely relabels the display zone. PostgreSQL (and DuckDB) make the operator asymmetric: `timestamptz AT TIME ZONE zone` returns a `timestamp` (naive) holding the wall clock in `zone`. Because DataFusion kept the value timezone-aware, casting the result to `::timestamp` produced the UTC wall clock instead of the zone's, which is what apache#12218 reports. The SQL planner now types the input and branches on it. The naive case is unchanged. The aware case relabels the instant into `tz` with the same cast and then drops the timezone while keeping the displayed value, which is exactly `to_local_time`. `datafusion-sql` must not depend on `datafusion-functions`, so that second half goes through a new `ExprPlanner::plan_at_time_zone` hook -- the same shape as `plan_extract` lowering `EXTRACT` to `date_part` -- implemented by `DatetimeFunctionPlanner`. Sessions without it get a clear planning error instead of the old silent mislowering. `AT TIME ZONE` also no longer forces `Nanosecond`: it keeps the input's `TimeUnit` when the input is a timestamp. Closes apache#12218 Co-Authored-By: Claude Opus 5 <[email protected]>
1 parent 582f872 commit 5df8177

5 files changed

Lines changed: 172 additions & 37 deletions

File tree

‎datafusion/expr/src/planner.rs‎

Lines changed: 20 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -218,6 +218,26 @@ pub trait ExprPlanner: Debug + Send + Sync {
218218
Ok(PlannerResult::Original(args))
219219
}
220220

221+
/// Plan `<expr> AT TIME ZONE '<tz>'` when `<expr>` is **already
222+
/// timezone-aware**.
223+
///
224+
/// Following PostgreSQL, `<tz-aware timestamp> AT TIME ZONE zone` returns
225+
/// the wall clock the instant has in `zone`, as a timezone-*naive*
226+
/// timestamp. (The timezone-*naive* case is the plain
227+
/// `CAST(<expr> AS Timestamp(unit, Some(tz)))` that the SQL planner
228+
/// handles on its own and never reaches this method.)
229+
///
230+
/// `args` holds a single expression: the input already cast to
231+
/// `Timestamp(unit, Some(tz))`. That cast preserves the instant and only
232+
/// relabels the timezone, so all an implementation has to do is drop the
233+
/// timezone while keeping the displayed value — exactly what
234+
/// `to_local_time` does.
235+
///
236+
/// Returns original expression arguments if not possible
237+
fn plan_at_time_zone(&self, args: Vec<Expr>) -> Result<PlannerResult<Vec<Expr>>> {
238+
Ok(PlannerResult::Original(args))
239+
}
240+
221241
/// Plans a struct literal, such as `{'field1' : expr1, 'field2' : expr2, ...}`
222242
///
223243
/// This function takes a vector of expressions and a boolean flag

‎datafusion/functions/src/datetime/planner.rs‎

Lines changed: 16 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -32,4 +32,20 @@ impl ExprPlanner for DatetimeFunctionPlanner {
3232
ScalarFunction::new_udf(crate::datetime::date_part(), args),
3333
)))
3434
}
35+
36+
/// `<tz-aware timestamp> AT TIME ZONE 'tz'` returns the wall clock in `tz`
37+
/// as a timezone-naive timestamp, matching PostgreSQL.
38+
///
39+
/// The SQL planner hands us the input already cast to
40+
/// `Timestamp(unit, Some(tz))` — an instant-preserving relabel — so the
41+
/// remaining step is to strip the timezone while keeping the displayed
42+
/// value, which is `to_local_time`.
43+
fn plan_at_time_zone(
44+
&self,
45+
args: Vec<Expr>,
46+
) -> datafusion_common::Result<PlannerResult<Vec<Expr>>> {
47+
Ok(PlannerResult::Planned(Expr::ScalarFunction(
48+
ScalarFunction::new_udf(crate::datetime::to_local_time(), args),
49+
)))
50+
}
3551
}

‎datafusion/sql/src/expr/mod.rs‎

Lines changed: 87 additions & 18 deletions
Original file line numberDiff line numberDiff line change
@@ -16,6 +16,7 @@
1616
// under the License.
1717

1818
use std::ops::ControlFlow;
19+
use std::sync::Arc;
1920

2021
use arrow::datatypes::{DataType, TimeUnit};
2122
use datafusion_expr::planner::{
@@ -750,24 +751,12 @@ impl<S: ContextProvider> SqlToRel<'_, S> {
750751
SQLExpr::AtTimeZone {
751752
timestamp,
752753
time_zone,
753-
} => Ok(Expr::Cast(Cast::new(
754-
Box::new(self.sql_expr_to_logical_expr_internal(
755-
*timestamp,
756-
schema,
757-
planner_context,
758-
)?),
759-
match *time_zone {
760-
SQLExpr::Value(ValueWithSpan {
761-
value: Value::SingleQuotedString(s),
762-
span: _,
763-
}) => DataType::Timestamp(TimeUnit::Nanosecond, Some(s.into())),
764-
_ => {
765-
return not_impl_err!(
766-
"Unsupported ast node in sqltorel: {time_zone:?}"
767-
);
768-
}
769-
},
770-
))),
754+
} => self.sql_at_time_zone_to_expr(
755+
*timestamp,
756+
*time_zone,
757+
schema,
758+
planner_context,
759+
),
771760
SQLExpr::Dictionary(fields) => {
772761
self.try_plan_dictionary_literal(fields, schema, planner_context)
773762
}
@@ -905,6 +894,86 @@ impl<S: ContextProvider> SqlToRel<'_, S> {
905894
}
906895
}
907896

897+
/// Plan `<timestamp> AT TIME ZONE '<tz>'`.
898+
///
899+
/// The meaning of `AT TIME ZONE` depends on whether its input carries a
900+
/// timezone, and it always returns the *other* kind of timestamp. This
901+
/// follows PostgreSQL (and DuckDB):
902+
///
903+
/// * a timezone-**naive** input is read as a wall clock in `tz`, and the
904+
/// result is the corresponding timezone-**aware** instant. That is a
905+
/// plain `CAST(expr AS Timestamp(unit, Some(tz)))`, because arrow's
906+
/// `Timestamp(_, None) -> Timestamp(_, Some(tz))` cast interprets the
907+
/// naive value as local time in `tz`.
908+
/// * a timezone-**aware** input is an instant, and the result is the wall
909+
/// clock that instant has in `tz`, as a timezone-**naive** timestamp.
910+
/// The same cast is still the first half of that (casting between two
911+
/// aware types preserves the instant and only relabels the zone); the
912+
/// second half — dropping the zone while keeping the displayed value —
913+
/// is delegated to [`ExprPlanner::plan_at_time_zone`], which
914+
/// `datafusion-functions` implements with `to_local_time`.
915+
///
916+
/// Anything that is not a timestamp (a string literal, for instance) takes
917+
/// the naive path, since a `CAST` to a timezone-aware timestamp is the
918+
/// natural reading of `AT TIME ZONE` for it.
919+
///
920+
/// [`ExprPlanner::plan_at_time_zone`]: datafusion_expr::planner::ExprPlanner::plan_at_time_zone
921+
fn sql_at_time_zone_to_expr(
922+
&self,
923+
timestamp: SQLExpr,
924+
time_zone: SQLExpr,
925+
schema: &DFSchema,
926+
planner_context: &mut PlannerContext,
927+
) -> Result<Expr> {
928+
let tz: Arc<str> = match time_zone {
929+
SQLExpr::Value(ValueWithSpan {
930+
value: Value::SingleQuotedString(s),
931+
span: _,
932+
}) => s.into(),
933+
_ => {
934+
return not_impl_err!("Unsupported ast node in sqltorel: {time_zone:?}");
935+
}
936+
};
937+
938+
let expr =
939+
self.sql_expr_to_logical_expr_internal(timestamp, schema, planner_context)?;
940+
941+
// `AT TIME ZONE` does not change the precision of its input, so keep
942+
// the input's `TimeUnit` when it has one.
943+
let (unit, input_is_tz_aware) = match expr.get_type(schema)? {
944+
DataType::Timestamp(unit, tz) => (unit, tz.is_some()),
945+
_ => (TimeUnit::Nanosecond, false),
946+
};
947+
948+
// Instant-preserving relabel into `tz` for an aware input; local-time
949+
// interpretation for a naive one.
950+
let relabeled = Expr::Cast(Cast::new(
951+
Box::new(expr),
952+
DataType::Timestamp(unit, Some(tz)),
953+
));
954+
955+
if !input_is_tz_aware {
956+
return Ok(relabeled);
957+
}
958+
959+
let mut args = vec![relabeled];
960+
for planner in self.context_provider.get_expr_planners() {
961+
match planner.plan_at_time_zone(args)? {
962+
PlannerResult::Planned(expr) => return Ok(expr),
963+
PlannerResult::Original(original) => {
964+
args = original;
965+
}
966+
}
967+
}
968+
969+
plan_err!(
970+
"AT TIME ZONE on a timezone-aware timestamp is not supported by any \
971+
ExprPlanner. It needs the `to_local_time` function; register \
972+
`datafusion_functions::datetime` (or its `DatetimeFunctionPlanner`) \
973+
with the session"
974+
)
975+
}
976+
908977
fn sql_position_to_expr(
909978
&self,
910979
substr_expr: SQLExpr,

‎datafusion/sql/tests/sql_integration.rs‎

Lines changed: 33 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -4045,6 +4045,39 @@ fn plan_merge_into_rejects_invalid_actions_and_structure(
40454045
);
40464046
}
40474047

4048+
/// A timezone-naive input is read as a wall clock in the target timezone, so it
4049+
/// lowers to a plain `CAST` and preserves the input's `TimeUnit`.
4050+
#[test]
4051+
fn plan_at_time_zone_on_naive_timestamp() {
4052+
let plan =
4053+
logical_plan("SELECT birth_date AT TIME ZONE 'America/Denver' FROM person")
4054+
.unwrap();
4055+
assert_snapshot!(
4056+
plan,
4057+
@r#"
4058+
Projection: CAST(person.birth_date AS Timestamp(ns, "America/Denver"))
4059+
TableScan: person
4060+
"#
4061+
);
4062+
}
4063+
4064+
/// A timezone-aware input needs `to_local_time`, which is provided by
4065+
/// `datafusion-functions`' `DatetimeFunctionPlanner`. The mock context provider
4066+
/// used by these tests does not register it, so planning must fail with a clear
4067+
/// message rather than silently falling back to the naive lowering.
4068+
#[test]
4069+
fn plan_at_time_zone_on_tz_aware_timestamp_without_planner() {
4070+
let err = logical_plan(
4071+
"SELECT (birth_date AT TIME ZONE 'UTC') AT TIME ZONE 'America/Denver' FROM person",
4072+
)
4073+
.unwrap_err();
4074+
assert!(
4075+
err.strip_backtrace()
4076+
.contains("AT TIME ZONE on a timezone-aware timestamp"),
4077+
"unexpected error: {err}"
4078+
);
4079+
}
4080+
40484081
fn logical_plan(sql: &str) -> Result<LogicalPlan> {
40494082
logical_plan_with_options(sql, ParserOptions::default())
40504083
}

‎datafusion/sqllogictest/test_files/datetime/timestamps.slt‎

Lines changed: 16 additions & 19 deletions
Original file line numberDiff line numberDiff line change
@@ -4281,9 +4281,7 @@ SELECT '2023-03-12 02:00:00' AT TIME ZONE 'EDT';
42814281
## * `<tz-aware timestamp> AT TIME ZONE zone` returns the wall clock that the
42824282
## instant has in `zone`, as a tz-*naive* timestamp.
42834283
##
4284-
## The expectations below record DataFusion's behaviour *before* the fix for
4285-
## #12218: the tz-aware case wrongly stays tz-aware, which only relabels the
4286-
## display zone. The following commit flips them to the PostgreSQL results.
4284+
## Every result below was checked against PostgreSQL 17.
42874285
##########
42884286

42894287
statement ok
@@ -4306,13 +4304,13 @@ Timestamp(ns, "America/Denver") 2024-01-01T12:00:00-07:00
43064304
query TP
43074305
SELECT arrow_typeof(tstz AT TIME ZONE 'America/Denver'), tstz AT TIME ZONE 'America/Denver' FROM at_tz_t;
43084306
----
4309-
Timestamp(ns, "America/Denver") 2024-01-01T05:00:00-07:00
4307+
Timestamp(ns) 2024-01-01T05:00:00
43104308

43114309
# The exact reproducer from #12218. PostgreSQL returns `2024-01-01 05:00:00`.
43124310
query P
43134311
SELECT (tstz AT TIME ZONE 'America/Denver')::timestamp FROM at_tz_t;
43144312
----
4315-
2024-01-01T12:00:00
4313+
2024-01-01T05:00:00
43164314

43174315
# Chained. PostgreSQL: `timestamptz` `2024-01-01 04:00:00+00`, i.e. the Denver
43184316
# wall clock (05:00) re-read as a Brussels wall clock.
@@ -4322,7 +4320,7 @@ SELECT
43224320
tstz AT TIME ZONE 'America/Denver' AT TIME ZONE 'Europe/Brussels'
43234321
FROM at_tz_t;
43244322
----
4325-
Timestamp(ns, "Europe/Brussels") 2024-01-01T13:00:00+01:00
4323+
Timestamp(ns, "Europe/Brussels") 2024-01-01T05:00:00+01:00
43264324

43274325
# Fixed offset. Note DataFusion follows the arrow/ISO-8601 sign convention here
43284326
# (`+05:30` is 5h30m *east* of UTC), while PostgreSQL applies the POSIX
@@ -4331,22 +4329,21 @@ Timestamp(ns, "Europe/Brussels") 2024-01-01T13:00:00+01:00
43314329
query TP
43324330
SELECT arrow_typeof(tstz AT TIME ZONE '+05:30'), tstz AT TIME ZONE '+05:30' FROM at_tz_t;
43334331
----
4334-
Timestamp(ns, "+05:30") 2024-01-01T17:30:00+05:30
4332+
Timestamp(ns) 2024-01-01T17:30:00
43354333

4336-
# `AT TIME ZONE` currently forces Nanosecond precision regardless of the input.
4334+
# `AT TIME ZONE` preserves the precision of its input.
43374335
query T
43384336
SELECT arrow_typeof(arrow_cast('2024-01-01T12:00:00Z', 'Timestamp(Microsecond, Some("UTC"))') AT TIME ZONE 'America/Denver');
43394337
----
4340-
Timestamp(ns, "America/Denver")
4338+
Timestamp(µs)
43414339

43424340
query T
43434341
SELECT arrow_typeof(arrow_cast('2024-01-01T12:00:00', 'Timestamp(Microsecond, None)') AT TIME ZONE 'America/Denver');
43444342
----
4345-
Timestamp(ns, "America/Denver")
4343+
Timestamp(µs, "America/Denver")
43464344

43474345
# A real (multi-row) tz-aware column, spanning both DST transitions in
4348-
# America/Denver. The wall clocks below all agree with PostgreSQL; it is only
4349-
# the type that is wrong today.
4346+
# America/Denver.
43504347
statement ok
43514348
CREATE TABLE at_tz_dst(ts timestamptz) AS VALUES
43524349
(arrow_cast('2024-01-01T12:00:00Z', 'Timestamp(Nanosecond, Some("UTC"))')),
@@ -4363,18 +4360,18 @@ SELECT
43634360
arrow_typeof(ts AT TIME ZONE 'America/Denver') AS denver_type
43644361
FROM at_tz_dst ORDER BY ts;
43654362
----
4366-
2024-01-01T12:00:00Z 2024-01-01T05:00:00-07:00 Timestamp(ns, "America/Denver")
4367-
2024-03-10T08:59:00Z 2024-03-10T01:59:00-07:00 Timestamp(ns, "America/Denver")
4368-
2024-03-10T09:00:00Z 2024-03-10T03:00:00-06:00 Timestamp(ns, "America/Denver")
4369-
2024-07-01T12:00:00Z 2024-07-01T06:00:00-06:00 Timestamp(ns, "America/Denver")
4370-
2024-11-03T07:59:00Z 2024-11-03T01:59:00-06:00 Timestamp(ns, "America/Denver")
4371-
2024-11-03T08:00:00Z 2024-11-03T01:00:00-07:00 Timestamp(ns, "America/Denver")
4363+
2024-01-01T12:00:00Z 2024-01-01T05:00:00 Timestamp(ns)
4364+
2024-03-10T08:59:00Z 2024-03-10T01:59:00 Timestamp(ns)
4365+
2024-03-10T09:00:00Z 2024-03-10T03:00:00 Timestamp(ns)
4366+
2024-07-01T12:00:00Z 2024-07-01T06:00:00 Timestamp(ns)
4367+
2024-11-03T07:59:00Z 2024-11-03T01:59:00 Timestamp(ns)
4368+
2024-11-03T08:00:00Z 2024-11-03T01:00:00 Timestamp(ns)
43724369

43734370
# `now()` is tz-aware whenever `datafusion.execution.time_zone` is set.
43744371
query T
43754372
SELECT arrow_typeof(now() AT TIME ZONE 'America/Denver');
43764373
----
4377-
Timestamp(ns, "America/Denver")
4374+
Timestamp(ns)
43784375

43794376
statement ok
43804377
DROP TABLE at_tz_dst;

0 commit comments

Comments
 (0)