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/10/19 20:40:09 UTC
(datafusion) branch gh-readonly-queue/main/pr-25165-364613296206dcffa62634b855dbbc4fd0f77b50 created (now 6cab074ec7)
This is an automated email from the ASF dual-hosted git repository.
github-merge-queue[bot] pushed a change to branch gh-readonly-queue/main/pr-25165-364613296206dcffa62634b855dbbc4fd0f77b50
in repository https://gitbox.apache.org/repos/asf/datafusion.git
at 6cab074ec7 fix: `AT TIME ZONE` on a timezone-aware timestamp returns a naive timestamp (#25165)
This branch includes the following new commits:
new 6cab074ec7 fix: `AT TIME ZONE` on a timezone-aware timestamp returns a naive timestamp (#25165)
The 1 revisions listed above as "new" are entirely new to this
repository and will be described in separate emails. The revisions
listed as "add" were already present in the repository and have only
been added to this reference.
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@datafusion.apache.org
For additional commands, e-mail: commits-help@datafusion.apache.org
(datafusion) 01/01: fix: `AT TIME ZONE` on a timezone-aware timestamp returns a naive timestamp (#25165)
Posted by gi...@apache.org.
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-25165-364613296206dcffa62634b855dbbc4fd0f77b50
in repository https://gitbox.apache.org/repos/asf/datafusion.git
commit 6cab074ec728ab81492085ed39f54601041a1860
Author: Adrian Garcia Badaracco <17...@users.noreply.github.com>
AuthorDate: Mon Oct 19 20:39:43 2026 +0000
fix: `AT TIME ZONE` on a timezone-aware timestamp returns a naive timestamp (#25165)
## Which issue does this PR close?
- Closes https://github.com/apache/datafusion/issues/12218
> [!NOTE]
> **Earlier limitation, now fixed.** A first version chose the lowering
in the SQL planner from `Expr::get_type`, which runs before type
coercion. So a `CASE` or `UNION` whose first arm is naive but whose
coerced type is aware kept the old result. `AT TIME ZONE` now plans as a
`timezone(zone, expr)` function that chooses its behaviour from the
coerced type. `CASE WHEN b THEN naive ELSE aware END AT TIME ZONE
'America/Denver'` and the same `CASE` with the arms swapped both return
the naive `2024-01-01T05:00:00`, as in PostgreSQL 17. The cases are
tested in `datetime/timestamps.slt`.
## Rationale for this change
### Start here: what a user gets today
Two terms run through this description:
- an **aware timestamp** carries a timezone, so it names an instant.
Arrow spells it `Timestamp(unit, Some(tz))`. SQL spells it
`timestamptz`.
- a **naive timestamp** carries no timezone, so it names only a wall
clock. Arrow spells it `Timestamp(unit, None)`. SQL spells it
`timestamp`.
This is the case from the issue. Noon UTC is 05:00 in Denver.
```sql
SET datafusion.execution.time_zone = 'UTC';
SELECT ('2024-01-01T12:00:00Z'::timestamptz AT TIME ZONE 'America/Denver')::timestamp;
```
| Engine | Result |
| --- | --- |
| DataFusion, today | `2024-01-01T12:00:00` |
| DataFusion, this PR | `2024-01-01T05:00:00` |
| PostgreSQL 17.11 | `2024-01-01 05:00:00` |
| DuckDB 1.5.2 | `2024-01-01 05:00:00` |
DataFusion returns the UTC wall clock, so it drops the timezone the user
asks for. Drop the final cast and the cause appears:
```sql
SELECT
'2024-01-01T12:00:00Z'::timestamptz AT TIME ZONE 'America/Denver' AS value,
arrow_typeof('2024-01-01T12:00:00Z'::timestamptz AT TIME ZONE 'America/Denver') AS type;
```
| Engine | Value | Type |
| --- | --- | --- |
| DataFusion, today | `2024-01-01T05:00:00-07:00` | `Timestamp(ns,
"America/Denver")` |
| DataFusion, this PR | `2024-01-01T05:00:00` | `Timestamp(ns)` |
| PostgreSQL 17.11 | `2024-01-01 05:00:00` | `timestamp without time
zone` |
| DuckDB 1.5.2 | `2024-01-01 05:00:00` | `TIMESTAMP` |
The displayed value looks right today, but the type is wrong. The result
stays aware, so the next operation on it reads the UTC wall clock again.
### Overview
**This is a breaking semantic change.** It changes the result type of
`AT TIME ZONE`, and so the result value of anything downstream, when the
input is an aware timestamp.
`AT TIME ZONE` is asymmetric in PostgreSQL. It always returns the other
kind of timestamp:
- a **naive** input becomes an **aware** timestamp. The engine reads the
value as a wall clock in the target timezone and returns that instant.
- an **aware** input becomes a **naive** timestamp. The engine reads off
the wall clock that the instant has in the target timezone.
DataFusion implements the first half only. This PR adds the second half.
The first half does not change.
### Why the current lowering is wrong
DataFusion lowers both halves to `CAST(expr AS Timestamp(Nanosecond,
Some(tz)))`.
- For a naive input that cast is correct. Arrow reads the naive value as
local time in `tz`.
- For an aware input that cast only relabels the display timezone. It
keeps the instant, so the result stays aware.
## What changes are included in this PR?
`AT TIME ZONE` is now the new scalar function `timezone(zone,
expression)`, the function form that PostgreSQL and DuckDB also have.
The function is in `datafusion/functions/src/datetime/timezone.rs`. It
chooses its behaviour from the type of `expression` after type coercion:
- `Timestamp(unit, None)`, or any type that is not a timestamp: the same
result as before, `CAST(expr AS Timestamp(unit, Some(tz)))`.
- `Timestamp(unit, Some(_))`: the same cast, then `to_local_time`, which
drops the timezone and keeps the wall clock.
- A dictionary-encoded timestamp follows its value type.
The function reads the type every time: in `return_field_from_args` for
the plan, and in `invoke_with_args` for the data. It is deliberately
never rewritten into the `CAST` form during planning, because the type
at that point is not always final:
- the SQL planner runs before type coercion, so the first `CASE` arm or
`UNION` branch can have a different type from the coerced result,
- `PREPARE` runs the optimizer without the analyzer,
- an untyped placeholder has no type until `EXECUTE`.
`zone` must be a constant, because it decides the result type. A
non-constant zone gives an error.
### The planner hook
`datafusion-sql` must not depend on `datafusion-functions`, so `AT TIME
ZONE` goes through a new trait method:
- `ExprPlanner::plan_at_time_zone` in `datafusion/expr/src/planner.rs`.
It receives `[Utf8 literal tz, expr]`, the argument order of
`timezone(zone, expr)`.
- implemented by `DatetimeFunctionPlanner` in
`datafusion/functions/src/datetime/planner.rs`, which returns the
`timezone` call,
- with a default `PlannerResult::Original` body, so out-of-tree
implementations still compile.
`plan_extract` lowers `EXTRACT` to `date_part` through the same seam, so
this follows an established pattern. The SQL planner
(`sql_at_time_zone_to_expr` in `datafusion/sql/src/expr/mod.rs`) no
longer reads the input type at all. The trait doc states that an
implementation must return an expression that decides after type
coercion.
A session that registers no such planner now gets a plan error for every
`AT TIME ZONE` input, as for `EXTRACT`.
### The `TimeUnit` change
`AT TIME ZONE` no longer forces `Nanosecond`. It keeps the input's
`TimeUnit` when the input is a timestamp.
| Input | Type today | Type with this PR |
| --- | --- | --- |
| `Timestamp(µs, "UTC")` | `Timestamp(ns, "America/Denver")` |
`Timestamp(µs)` |
| `Timestamp(µs, None)` | `Timestamp(ns, "America/Denver")` |
`Timestamp(µs, "America/Denver")` |
| `Timestamp(s, "UTC")` | `Timestamp(ns, "America/Denver")` |
`Timestamp(s)` |
| `Utf8`, `Date32`, and other non-timestamps | `Timestamp(ns,
"America/Denver")` | `Timestamp(ns, "America/Denver")` |
PostgreSQL keeps the precision of its input too. `timestamptz
'2024-01-01 12:00:00.123456Z' AT TIME ZONE 'America/Denver'` returns
`2024-01-01 05:00:00.123456`.
### Not changed
- **Spark.** `SparkFunctionPlanner` implements `plan_extract` and
`plan_substring` only, and `with_spark_features` inserts it at index 0
of the list that `with_default_features` builds. So `plan_at_time_zone`
falls through to `DatetimeFunctionPlanner`, and no Spark-specific
function or planner behaviour changes. A Spark-flavoured session that
uses DataFusion's SQL planner does get the new `AT TIME ZONE` semantics,
but Spark SQL has no `AT TIME ZONE` syntax. It uses `from_utc_timestamp`
and `to_utc_timestamp`. Nothing under `datafusion/spark/` implements,
tests or documents the operator.
- **`AT LOCAL`.** PostgreSQL 17 supports it. sqlparser 0.62.0 has no
`AtLocal` AST node and rejects the syntax, so there is nothing to route.
- **Fixed-offset strings.** See the field research below. Only the
result type changes for these.
- **String-literal inputs.** `'2000-12-01T04:04:12-05:00' AT TIME ZONE
'x'` still takes the naive path, because `Utf8` is not a timestamp type.
That keeps today's behaviour: DataFusion honours the offset inside the
string. PostgreSQL resolves the unknown-typed literal to `timestamp` and
drops the offset. This gap is older than this PR and is separate from
#12218.
## What is the testing strategy for this PR?
### Field research
I measured two reference engines rather than trust the documentation:
- PostgreSQL 17.11, `docker run --rm -e POSTGRES_PASSWORD=pw -p
55452:5432 postgres:17`, with `SET TimeZone='UTC'`,
- DuckDB 1.5.2 CLI, with `SET TimeZone='UTC'`.
The DataFusion columns come from a `datafusion-cli` build of `main` and
of this branch, with `SET datafusion.execution.time_zone = 'UTC'`.
#### The four core cases
| Case | DataFusion, today | DataFusion, this PR | PostgreSQL 17.11 |
DuckDB 1.5.2 |
| --- | --- | --- | --- | --- |
| naive input:<br>`'2024-01-01 12:00:00'::timestamp AT TIME ZONE
'America/Denver'` | `2024-01-01T12:00:00-07:00`<br>`Timestamp(ns,
"America/Denver")` | same as today | `2024-01-01
19:00:00+00`<br>`timestamp with time zone` | `2024-01-01
19:00:00+00`<br>`TIMESTAMP WITH TIME ZONE` |
| aware input:<br>`'2024-01-01T12:00:00Z'::timestamptz AT TIME ZONE
'America/Denver'` | `2024-01-01T05:00:00-07:00`<br>`Timestamp(ns,
"America/Denver")` | `2024-01-01T05:00:00`<br>`Timestamp(ns)` |
`2024-01-01 05:00:00`<br>`timestamp without time zone` | `2024-01-01
05:00:00`<br>`TIMESTAMP` |
| the issue reproducer:<br>`(<aware> AT TIME ZONE
'America/Denver')::timestamp` | `2024-01-01T12:00:00` |
`2024-01-01T05:00:00` | `2024-01-01 05:00:00` | `2024-01-01 05:00:00` |
| chained:<br>`<aware> AT TIME ZONE 'America/Denver' AT TIME ZONE
'Europe/Brussels'` | `2024-01-01T13:00:00+01:00`<br>= 12:00 UTC |
`2024-01-01T05:00:00+01:00`<br>= 04:00 UTC | `2024-01-01 04:00:00+00` |
`2024-01-01 04:00:00+00` |
The naive row agrees across all four columns. `12:00` in Denver is
`19:00` UTC. Only the display convention differs.
The chained row is the one that changes value, not just type. Today
DataFusion relabels twice and the instant never moves. With this PR the
Denver wall clock (05:00) becomes a Brussels wall clock, which is 04:00
UTC. PostgreSQL and DuckDB both return 04:00 UTC.
#### Both 2024 DST transitions in `America/Denver`, aware input
| UTC instant | DataFusion, today | DataFusion, this PR | PostgreSQL
17.11 | DuckDB 1.5.2 |
| --- | --- | --- | --- | --- |
| `2024-01-01T12:00:00Z` | `2024-01-01T05:00:00-07:00` |
`2024-01-01T05:00:00` | `2024-01-01 05:00:00` | `2024-01-01 05:00:00` |
| `2024-03-10T08:59:00Z` | `2024-03-10T01:59:00-07:00` |
`2024-03-10T01:59:00` | `2024-03-10 01:59:00` | `2024-03-10 01:59:00` |
| `2024-03-10T09:00:00Z` | `2024-03-10T03:00:00-06:00` |
`2024-03-10T03:00:00` | `2024-03-10 03:00:00` | `2024-03-10 03:00:00` |
| `2024-07-01T12:00:00Z` | `2024-07-01T06:00:00-06:00` |
`2024-07-01T06:00:00` | `2024-07-01 06:00:00` | `2024-07-01 06:00:00` |
| `2024-11-03T07:59:00Z` | `2024-11-03T01:59:00-06:00` |
`2024-11-03T01:59:00` | `2024-11-03 01:59:00` | `2024-11-03 01:59:00` |
| `2024-11-03T08:00:00Z` | `2024-11-03T01:00:00-07:00` |
`2024-11-03T01:00:00` | `2024-11-03 01:00:00` | `2024-11-03 01:00:00` |
The rows cover the spring-forward gap (`01:59` jumps to `03:00`) and the
fall-back overlap (`01:59` MDT, then `01:00` MST). All four columns
agree on the wall clock. Only the DataFusion type changes.
#### The DST gap and overlap from the other side, naive input
This is the branch that this PR does not touch. It is here so the table
stays honest.
| Case | DataFusion, today and with this PR | PostgreSQL 17.11 | DuckDB
1.5.2 |
| --- | --- | --- | --- |
| gap: `'2024-03-10 02:30:00'::timestamp AT TIME ZONE 'America/Denver'`
| `Arrow error: Cast error: Cannot cast timezone to different timezone`
| `2024-03-10 09:30:00+00` | `2024-03-10 09:30:00+00` |
| overlap: `'2024-11-03 01:30:00'::timestamp AT TIME ZONE
'America/Denver'` | same error | `2024-11-03 08:30:00+00` | `2024-11-03
08:30:00+00` |
A bare `arrow_cast` to `Timestamp(Nanosecond, Some("America/Denver"))`
reproduces the error, so it comes from the arrow cast and not from the
SQL planner. This PR does not change it.
#### The case the two engines cannot arbitrate: a fixed-offset string
| Case | DataFusion, today | DataFusion, this PR | PostgreSQL 17.11 |
DuckDB 1.5.2 |
| --- | --- | --- | --- | --- |
| `<aware> AT TIME ZONE '+05:30'` |
`2024-01-01T17:30:00+05:30`<br>`Timestamp(ns, "+05:30")` |
`2024-01-01T17:30:00`<br>`Timestamp(ns)` | `2024-01-01
06:30:00`<br>`timestamp without time zone` | `Not implemented Error:
Unknown TimeZone '+05:30'!` |
DataFusion follows arrow and ISO 8601, where `+05:30` is east of UTC.
PostgreSQL applies the POSIX convention to an offset spelled as a string
and reads `+05:30` as west of UTC. DuckDB rejects the string and accepts
IANA names only. So DataFusion's reading matches neither engine.
PostgreSQL does agree with DataFusion when the offset carries a type:
```sql
postgres=# SELECT timestamptz '2024-01-01 12:00:00Z' AT TIME ZONE interval '+05:30';
2024-01-01 17:30:00
```
This divergence already exists on main. It is separate from #12218 and
out of scope here. It is tracked in
https://github.com/apache/datafusion/issues/25170. This PR changes only
the result type for this case, not the value.
### Tests
New `sqllogictest` coverage in
`datafusion/sqllogictest/test_files/datetime/timestamps.slt`. The first
commit adds it as a characterization of today's behaviour. The second
commit flips it, so the diff shows exactly what changes. Every case
asserts `arrow_typeof` next to the value, and I checked every expected
value against PostgreSQL 17 by hand. I did not regenerate them with
`--complete`. The cases are:
- a naive column and an aware column,
- the exact `::timestamp` reproducer from #12218,
- two chained `AT TIME ZONE` applications,
- a fixed-offset zone,
- microsecond precision in both directions,
- `now()`,
- typed `NULL::timestamp` and `NULL::timestamptz` inputs,
- a multi-row aware column that spans both 2024 DST transitions in
`America/Denver`,
- `CASE`, `UNION ALL`, a CTE, a view and an aggregate whose first arm is
naive but whose coerced type is aware, including the `TimeUnit` of the
coerced type,
- a dictionary-encoded timestamp,
- `PREPARE` / `EXECUTE` with an untyped placeholder and with a `CASE`,
- the `EXPLAIN` plan shape and the column name,
- the `timezone` function form, and the error for a non-constant zone.
A unit test for `TimezoneFunc::invoke_with_args` in both directions.
Two planner tests in `datafusion/sql/tests/sql_integration.rs`: the plan
shape with `DatetimeFunctionPlanner` (a `timezone` call for every input
type), and the error when no `ExprPlanner` handles `AT TIME ZONE`.
Two existing expectations change:
- the `to_local_time` invalid-timezone error no longer comes from the
`simplify_expressions` rule, so its prefix changes,
- `t_europe WHERE column1 = '2024-01-31T16:00:01' AT TIME ZONE
'America/Los_Angeles'` loses a row that never matched. Before, `AT TIME
ZONE` was a `CAST`, and `unwrap_cast_in_comparison` relabelled the
literal instead of converting it
(https://github.com/apache/datafusion/issues/25095). DuckDB also returns
no row.
Green locally:
- the full `sqllogictest` suite,
- `cargo test -p datafusion-sql`,
- `cargo test -p datafusion-functions timezone` and `to_local_time`,
- `cargo fmt --all` and `cargo clippy --all-targets -- -D warnings`,
- the repo's extended workspace suite (`--features
avro,json,backtrace,extended_tests,recursive_protection,parquet_encryption`).
One caveat. `AT TIME ZONE` appeared in exactly one test file on main
before https://github.com/apache/datafusion/pull/25175, and every use
there had a `Utf8` or naive inner expression. So the corpus had no
coverage of the broken case at all, which is why the bug survived from
#9647 in DataFusion 37. Few changed expectations is a weak safety signal
here, not a strong one. The field research above carries the argument
instead.
## Are there any user-facing changes?
**Yes. This is a breaking change to `AT TIME ZONE` semantics.** The `api
change` label is applied.
The changes, in order of impact:
1. An **aware** input now returns a **naive** timestamp, per the tables
above. Any expression downstream of it changes value.
2. `AT TIME ZONE` keeps the input's `TimeUnit` instead of a forced
`Nanosecond`.
3. An unaliased projection changes its column name for every input.
`SELECT aware AT TIME ZONE 'America/Denver' FROM t` now names the column
`t.aware AT TIME ZONE 'America/Denver'`.
4. The logical and physical plans show `timezone(zone, expr)` instead of
a `CAST`. So `unwrap_cast_in_comparison` no longer applies to it, which
fixes a wrong match (https://github.com/apache/datafusion/issues/25095)
for a literal compared with `naive AT TIME ZONE 'zone'`.
5. A session that does not register the datetime planner from
`datafusion-functions` now gets a plan error for `AT TIME ZONE`, for
every input type.
6. `ExprPlanner` gains a `plan_at_time_zone` method. It has a default
body, so out-of-tree implementations still compile.
7. A new scalar function `timezone(zone, expression)`.
8. New `AT TIME ZONE` section in
`docs/source/user-guide/sql/operators.md`, and a `timezone` entry in
`scalar_functions.md`. The `to_local_time` description says how they
relate.
A **naive** input does not change. Its value and its timezone label stay
the same. Only its column name and plan shape change.
**Maintainer input wanted:** does this land as a straight behaviour fix,
or behind a config flag with a deprecation period? I deliberately added
no flag. The current behaviour has no defensible justification, and a
flag means two timestamp typings through the planner for a long time.
But this changes results for anyone who relies on the old shape, so I
would rather you decide than assume.
**Interaction with https://github.com/apache/datafusion/pull/25175:**
that PR has merged. Its suite
`test_files/datetime/timestamps_timezone.slt` pinned the old behaviour
in SECTION 5b. This PR updates SECTION 5b to the naive results and
removes the `DIVERGES FROM POSTGRESQL (type, not instant)` comment,
because this PR removes that divergence. The `KNOWN WRONG` comment that
follows now refers only to
https://github.com/apache/datafusion/issues/25166.
🤖 Generated with [Claude Code](https://claude.com/claude-code)
---------
Co-authored-by: Claude Opus 5 <no...@anthropic.com>
---
datafusion/expr/src/planner.rs | 15 ++
datafusion/functions/src/datetime/mod.rs | 3 +
datafusion/functions/src/datetime/planner.rs | 10 +
datafusion/functions/src/datetime/timezone.rs | 258 ++++++++++++++++++
datafusion/functions/src/datetime/to_local_time.rs | 8 +-
datafusion/sql/src/expr/mod.rs | 71 +++--
datafusion/sql/tests/sql_integration.rs | 39 +++
.../test_files/datetime/timestamps.slt | 294 ++++++++++++++++++++-
.../test_files/datetime/timestamps_timezone.slt | 30 +--
docs/source/user-guide/sql/operators.md | 64 +++++
docs/source/user-guide/sql/scalar_functions.md | 43 +++
11 files changed, 795 insertions(+), 40 deletions(-)
diff --git a/datafusion/expr/src/planner.rs b/datafusion/expr/src/planner.rs
index ed13209521..637a3e3d15 100644
--- a/datafusion/expr/src/planner.rs
+++ b/datafusion/expr/src/planner.rs
@@ -218,6 +218,21 @@ pub trait ExprPlanner: Debug + Send + Sync {
Ok(PlannerResult::Original(args))
}
+ /// Plan `<expr> AT TIME ZONE '<tz>'`.
+ ///
+ /// `args` is `[Utf8 literal tz, expr]`, the argument order of the
+ /// PostgreSQL function form `timezone(zone, expr)`.
+ ///
+ /// The result type depends on whether the type of `expr` *after type
+ /// coercion* has a timezone, and the SQL planner runs before type
+ /// coercion. So an implementation must return an expression that decides
+ /// later, such as the `timezone` function of `datafusion-functions`.
+ ///
+ /// Returns original expression arguments if not possible
+ fn plan_at_time_zone(&self, args: Vec<Expr>) -> Result<PlannerResult<Vec<Expr>>> {
+ Ok(PlannerResult::Original(args))
+ }
+
/// Plans a struct literal, such as `{'field1' : expr1, 'field2' : expr2, ...}`
///
/// This function takes a vector of expressions and a boolean flag
diff --git a/datafusion/functions/src/datetime/mod.rs b/datafusion/functions/src/datetime/mod.rs
index 9246d1d25d..e3b347e820 100644
--- a/datafusion/functions/src/datetime/mod.rs
+++ b/datafusion/functions/src/datetime/mod.rs
@@ -32,6 +32,7 @@ pub mod make_date;
pub mod make_time;
pub mod now;
pub mod planner;
+pub mod timezone;
pub mod to_char;
pub mod to_date;
pub mod to_local_time;
@@ -47,6 +48,7 @@ make_udf_function!(date_part::DatePartFunc, date_part);
make_udf_function!(date_trunc::DateTruncFunc, date_trunc);
make_udf_function!(make_date::MakeDateFunc, make_date);
make_udf_function!(make_time::MakeTimeFunc, make_time);
+make_udf_function!(timezone::TimezoneFunc, timezone);
make_udf_function!(to_char::ToCharFunc, to_char);
make_udf_function!(to_date::ToDateFunc, to_date);
make_udf_function!(to_local_time::ToLocalTimeFunc, to_local_time);
@@ -285,6 +287,7 @@ pub fn functions() -> Vec<Arc<ScalarUDF>> {
make_date(),
make_time(),
now(&config),
+ timezone(),
to_char(),
to_date(),
to_local_time(),
diff --git a/datafusion/functions/src/datetime/planner.rs b/datafusion/functions/src/datetime/planner.rs
index f2b8ef9d1d..18377467a8 100644
--- a/datafusion/functions/src/datetime/planner.rs
+++ b/datafusion/functions/src/datetime/planner.rs
@@ -32,4 +32,14 @@ impl ExprPlanner for DatetimeFunctionPlanner {
ScalarFunction::new_udf(crate::datetime::date_part(), args),
)))
}
+
+ /// `<expr> AT TIME ZONE 'tz'` is `timezone('tz', <expr>)`.
+ fn plan_at_time_zone(
+ &self,
+ args: Vec<Expr>,
+ ) -> datafusion_common::Result<PlannerResult<Vec<Expr>>> {
+ Ok(PlannerResult::Planned(Expr::ScalarFunction(
+ ScalarFunction::new_udf(crate::datetime::timezone(), args),
+ )))
+ }
}
diff --git a/datafusion/functions/src/datetime/timezone.rs b/datafusion/functions/src/datetime/timezone.rs
new file mode 100644
index 0000000000..7a0ee683b1
--- /dev/null
+++ b/datafusion/functions/src/datetime/timezone.rs
@@ -0,0 +1,258 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+use std::sync::Arc;
+
+use arrow::datatypes::{DataType, Field, FieldRef, TimeUnit};
+use datafusion_common::types::logical_string;
+use datafusion_common::utils::take_function_args;
+use datafusion_common::{Result, ScalarValue, exec_err, internal_err};
+use datafusion_expr::{
+ Coercion, ColumnarValue, Documentation, Expr, ReturnFieldArgs, ScalarFunctionArgs,
+ ScalarUDFImpl, Signature, TypeSignatureClass, Volatility,
+};
+use datafusion_macros::user_doc;
+
+/// `timezone(zone, expression)`: the function form of the SQL operator
+/// `expression AT TIME ZONE zone`, with the semantics of PostgreSQL and DuckDB.
+///
+/// The operator always returns the *other* kind of timestamp:
+///
+/// | Input type | Result | Computed as |
+/// | --- | --- | --- |
+/// | `Timestamp(unit, Some(_))` (aware) | the wall clock of the instant in `zone`, as `Timestamp(unit, None)` | `to_local_time(CAST(expression AS Timestamp(unit, Some(zone))))` |
+/// | `Timestamp(unit, None)` (naive) | the value read as a wall clock in `zone`, as `Timestamp(unit, Some(zone))` | `CAST(expression AS Timestamp(unit, Some(zone)))` |
+/// | any other type | as for a naive input | `CAST(expression AS Timestamp(ns, Some(zone)))` |
+///
+/// The choice depends only on the type of `expression`, and it is made every
+/// time that type is read: [`ScalarUDFImpl::return_field_from_args`] for the
+/// plan, and [`ScalarUDFImpl::invoke_with_args`] for the data. The function is
+/// deliberately never rewritten into the `CAST` form during planning. A rewrite
+/// would fix the choice from the type at that moment, and that type is not
+/// always final: the SQL planner runs before type coercion, `PREPARE` runs the
+/// optimizer without the analyzer, and an untyped placeholder has no type
+/// until `EXECUTE`.
+///
+/// `zone` must be a constant, because it determines the result type.
+#[user_doc(
+ doc_section(label = "Time and Date Functions"),
+ description = r#"Converts a timestamp to another timezone. This is the function form of `expression AT TIME ZONE zone`, and it follows PostgreSQL and DuckDB:
+
+- A timestamp **with** a timezone is an instant. The result is the wall-clock time of that instant in `zone`, as a timestamp **without** a timezone.
+- A timestamp **without** a timezone is a wall-clock time. The result is the instant at which that wall-clock time occurs in `zone`, as a timestamp **with** the timezone `zone`.
+- Any other type (for example a string) is converted as for a timestamp without a timezone.
+
+The result keeps the time unit of a timestamp input."#,
+ syntax_example = "timezone(zone, expression)",
+ sql_example = r#"```sql
+> SET datafusion.execution.time_zone = 'UTC';
+> SELECT timezone('America/Denver', '2024-01-01T12:00:00Z'::timestamptz) AS denver_wall_clock;
++---------------------+
+| denver_wall_clock |
++---------------------+
+| 2024-01-01T05:00:00 |
++---------------------+
+
+> SELECT timezone('America/Denver', '2024-01-01T12:00:00'::timestamp) AS denver_instant;
++---------------------------+
+| denver_instant |
++---------------------------+
+| 2024-01-01T12:00:00-07:00 |
++---------------------------+
+```"#,
+ argument(
+ name = "zone",
+ description = "Target timezone, as a constant string. An IANA timezone name such as `'America/Denver'`, or a fixed offset such as `'+05:30'`."
+ ),
+ argument(
+ name = "expression",
+ description = "Timestamp expression to convert. Can be a constant, column, or function."
+ )
+)]
+#[derive(Debug, PartialEq, Eq, Hash)]
+pub struct TimezoneFunc {
+ signature: Signature,
+}
+
+impl Default for TimezoneFunc {
+ fn default() -> Self {
+ Self::new()
+ }
+}
+
+impl TimezoneFunc {
+ pub fn new() -> Self {
+ Self {
+ signature: Signature::coercible(
+ vec![
+ Coercion::new_exact(TypeSignatureClass::Native(logical_string())),
+ Coercion::new_exact(TypeSignatureClass::Any),
+ ],
+ Volatility::Immutable,
+ ),
+ }
+ }
+}
+
+/// The two steps that `timezone(zone, <input_type>)` does: the target type of
+/// the `CAST`, and whether `to_local_time` follows it.
+fn lowering(input_type: &DataType, zone: &str) -> (DataType, bool) {
+ let (unit, input_is_aware) = match input_type {
+ DataType::Timestamp(unit, tz) => (*unit, tz.is_some()),
+ DataType::Dictionary(_, value_type) => match value_type.as_ref() {
+ DataType::Timestamp(unit, tz) => (*unit, tz.is_some()),
+ _ => (TimeUnit::Nanosecond, false),
+ },
+ _ => (TimeUnit::Nanosecond, false),
+ };
+ (DataType::Timestamp(unit, Some(zone.into())), input_is_aware)
+}
+
+fn zone_from_scalar(name: &str, zone: Option<&ScalarValue>) -> Result<String> {
+ match zone.and_then(|s| s.try_as_str().flatten()) {
+ Some(s) if !s.is_empty() => Ok(s.to_string()),
+ _ => exec_err!(
+ "{name} requires its first argument (the timezone) to be a constant, non-empty string"
+ ),
+ }
+}
+
+impl ScalarUDFImpl for TimezoneFunc {
+ fn name(&self) -> &str {
+ "timezone"
+ }
+
+ /// Name the column with the SQL operator syntax, so that
+ /// `x AT TIME ZONE 'tz'` names its column `x AT TIME ZONE 'tz'`.
+ fn schema_name(&self, args: &[Expr]) -> Result<String> {
+ let [zone, input] = take_function_args(self.name(), args)?;
+ let zone = match zone {
+ Expr::Literal(sv, _) => match sv.try_as_str().flatten() {
+ Some(s) => format!("'{s}'"),
+ None => zone.schema_name().to_string(),
+ },
+ _ => zone.schema_name().to_string(),
+ };
+ Ok(format!("{} AT TIME ZONE {zone}", input.schema_name()))
+ }
+
+ fn signature(&self) -> &Signature {
+ &self.signature
+ }
+
+ fn return_type(&self, _arg_types: &[DataType]) -> Result<DataType> {
+ internal_err!("return_field_from_args should be called instead")
+ }
+
+ fn return_field_from_args(&self, args: ReturnFieldArgs) -> Result<FieldRef> {
+ let [_, input] = take_function_args(self.name(), args.arg_fields)?;
+ let zone = zone_from_scalar(self.name(), args.scalar_arguments[0])?;
+ let (cast_type, strip_timezone) = lowering(input.data_type(), &zone);
+ let return_type = match cast_type {
+ DataType::Timestamp(unit, _) if strip_timezone => {
+ DataType::Timestamp(unit, None)
+ }
+ other => other,
+ };
+ Ok(Arc::new(Field::new(
+ self.name(),
+ return_type,
+ input.is_nullable(),
+ )))
+ }
+
+ fn invoke_with_args(&self, args: ScalarFunctionArgs) -> Result<ColumnarValue> {
+ let [zone, input] = take_function_args(self.name(), &args.args)?;
+ let zone = match zone {
+ ColumnarValue::Scalar(zone) => zone_from_scalar(self.name(), Some(zone))?,
+ ColumnarValue::Array(_) => zone_from_scalar(self.name(), None)?,
+ };
+ let (cast_type, strip_timezone) = lowering(&input.data_type(), &zone);
+ let cast = input.cast_to(&cast_type, None)?;
+ if strip_timezone {
+ super::to_local_time::to_local_time(&cast)
+ } else {
+ Ok(cast)
+ }
+ }
+
+ fn documentation(&self) -> Option<&Documentation> {
+ self.doc()
+ }
+}
+
+#[cfg(test)]
+mod tests {
+ use std::sync::Arc;
+
+ use arrow::array::{Array, ArrayRef, TimestampMicrosecondArray};
+ use arrow::datatypes::{DataType, Field, TimeUnit};
+ use datafusion_common::ScalarValue;
+ use datafusion_common::config::ConfigOptions;
+ use datafusion_expr::{ColumnarValue, ScalarFunctionArgs, ScalarUDFImpl};
+
+ use super::TimezoneFunc;
+
+ #[test]
+ fn invoke() {
+ // 2024-01-01T12:00:00Z
+ let micros = 1_704_110_400_000_000;
+ let cases = [
+ // aware input -> naive wall clock in Denver (05:00)
+ (
+ Some("UTC"),
+ DataType::Timestamp(TimeUnit::Microsecond, None),
+ micros - 7 * 3_600_000_000,
+ ),
+ // naive input -> the instant of 12:00 in Denver (19:00 UTC)
+ (
+ None,
+ DataType::Timestamp(TimeUnit::Microsecond, Some("America/Denver".into())),
+ micros + 7 * 3_600_000_000,
+ ),
+ ];
+ for (input_tz, expected_type, expected_value) in cases {
+ let input: ArrayRef = Arc::new(
+ TimestampMicrosecondArray::from(vec![micros]).with_timezone_opt(input_tz),
+ );
+ let args = ScalarFunctionArgs {
+ args: vec![
+ ColumnarValue::Scalar(ScalarValue::from("America/Denver")),
+ ColumnarValue::Array(Arc::clone(&input)),
+ ],
+ arg_fields: vec![
+ Arc::new(Field::new("zone", DataType::Utf8, false)),
+ Arc::new(Field::new("input", input.data_type().clone(), false)),
+ ],
+ number_rows: 1,
+ return_field: Arc::new(Field::new("f", expected_type.clone(), false)),
+ config_options: Arc::new(ConfigOptions::default()),
+ };
+ let ColumnarValue::Array(result) =
+ TimezoneFunc::new().invoke_with_args(args).unwrap()
+ else {
+ panic!("expected an array");
+ };
+ assert_eq!(result.data_type(), &expected_type);
+ let result = result
+ .as_any()
+ .downcast_ref::<TimestampMicrosecondArray>()
+ .unwrap();
+ assert_eq!(result.value(0), expected_value);
+ }
+ }
+}
diff --git a/datafusion/functions/src/datetime/to_local_time.rs b/datafusion/functions/src/datetime/to_local_time.rs
index 973afa549e..10e61a3b57 100644
--- a/datafusion/functions/src/datetime/to_local_time.rs
+++ b/datafusion/functions/src/datetime/to_local_time.rs
@@ -44,7 +44,11 @@ use datafusion_macros::user_doc;
/// while keep the display value of the timestamp the same.
#[user_doc(
doc_section(label = "Time and Date Functions"),
- description = "Converts a timestamp with a timezone to a timestamp without a timezone (with no offset or timezone information). This function handles daylight saving time changes.",
+ description = r#"Converts a timestamp with a timezone to a timestamp without a timezone (with no offset or timezone information). This function handles daylight saving time changes.
+
+A timestamp that already has no timezone is returned unchanged.
+
+`to_local_time` uses the timezone that the value already has. To get the wall-clock time in a timezone that you name in the query, use [`timezone(zone, expression)`](#timezone) or `expression AT TIME ZONE zone`: for a timestamp with a timezone, both return the wall-clock time in `zone`, without a timezone."#,
syntax_example = "to_local_time(expression)",
sql_example = r#"```sql
> SELECT to_local_time('2024-04-01T00:00:20Z'::timestamp);
@@ -158,7 +162,7 @@ fn transform_array<T: ArrowTimestampType>(
Ok(ColumnarValue::Array(Arc::new(result)))
}
-fn to_local_time(time_value: &ColumnarValue) -> Result<ColumnarValue> {
+pub(crate) fn to_local_time(time_value: &ColumnarValue) -> Result<ColumnarValue> {
let arg_type = time_value.data_type();
let tz: Tz = match &arg_type {
diff --git a/datafusion/sql/src/expr/mod.rs b/datafusion/sql/src/expr/mod.rs
index 40bd8bb71d..69d423acc9 100644
--- a/datafusion/sql/src/expr/mod.rs
+++ b/datafusion/sql/src/expr/mod.rs
@@ -16,6 +16,7 @@
// under the License.
use std::ops::ControlFlow;
+use std::sync::Arc;
use arrow::datatypes::{DataType, TimeUnit};
use datafusion_expr::planner::{
@@ -760,24 +761,12 @@ impl<S: ContextProvider> SqlToRel<'_, S> {
SQLExpr::AtTimeZone {
timestamp,
time_zone,
- } => Ok(Expr::Cast(Cast::new(
- Box::new(self.sql_expr_to_logical_expr_internal(
- *timestamp,
- schema,
- planner_context,
- )?),
- match *time_zone {
- SQLExpr::Value(ValueWithSpan {
- value: Value::SingleQuotedString(s),
- span: _,
- }) => DataType::Timestamp(TimeUnit::Nanosecond, Some(s.into())),
- _ => {
- return not_impl_err!(
- "Unsupported ast node in sqltorel: {time_zone:?}"
- );
- }
- },
- ))),
+ } => self.sql_at_time_zone_to_expr(
+ *timestamp,
+ *time_zone,
+ schema,
+ planner_context,
+ ),
SQLExpr::Dictionary(fields) => {
self.try_plan_dictionary_literal(fields, schema, planner_context)
}
@@ -967,6 +956,52 @@ impl<S: ContextProvider> SqlToRel<'_, S> {
}
}
+ /// Plan `<timestamp> AT TIME ZONE '<tz>'`.
+ ///
+ /// The result type depends on whether the input has a timezone *after
+ /// type coercion*, and type coercion runs after this planner. So this
+ /// method only builds `timezone('<tz>', <timestamp>)` through
+ /// [`ExprPlanner::plan_at_time_zone`], and that function decides once the
+ /// type is known. See `TimezoneFunc` in `datafusion-functions` for the
+ /// semantics.
+ ///
+ /// [`ExprPlanner::plan_at_time_zone`]: datafusion_expr::planner::ExprPlanner::plan_at_time_zone
+ fn sql_at_time_zone_to_expr(
+ &self,
+ timestamp: SQLExpr,
+ time_zone: SQLExpr,
+ schema: &DFSchema,
+ planner_context: &mut PlannerContext,
+ ) -> Result<Expr> {
+ let tz: Arc<str> = match time_zone {
+ SQLExpr::Value(ValueWithSpan {
+ value: Value::SingleQuotedString(s),
+ span: _,
+ }) => s.into(),
+ _ => {
+ return not_impl_err!("Unsupported ast node in sqltorel: {time_zone:?}");
+ }
+ };
+
+ let expr =
+ self.sql_expr_to_logical_expr_internal(timestamp, schema, planner_context)?;
+
+ let mut args = vec![lit(tz.as_ref()), expr];
+ for planner in self.context_provider.get_expr_planners() {
+ match planner.plan_at_time_zone(args)? {
+ PlannerResult::Planned(expr) => return Ok(expr),
+ PlannerResult::Original(original) => {
+ args = original;
+ }
+ }
+ }
+
+ not_impl_err!(
+ "AT TIME ZONE needs an ExprPlanner that implements `plan_at_time_zone`, \
+ such as the `DatetimeFunctionPlanner` of `datafusion-functions`"
+ )
+ }
+
fn sql_position_to_expr(
&self,
substr_expr: SQLExpr,
diff --git a/datafusion/sql/tests/sql_integration.rs b/datafusion/sql/tests/sql_integration.rs
index 7ab57a86f9..18dea42b39 100644
--- a/datafusion/sql/tests/sql_integration.rs
+++ b/datafusion/sql/tests/sql_integration.rs
@@ -46,6 +46,7 @@ use datafusion_sql::{
use crate::common::{CustomExprPlanner, CustomTypePlanner, MockSessionState};
use datafusion_functions::core::planner::CoreFunctionPlanner;
+use datafusion_functions::datetime::{self, planner::DatetimeFunctionPlanner};
use datafusion_functions_aggregate::{
approx_median::approx_median_udaf,
average::avg_udaf,
@@ -4063,6 +4064,44 @@ fn plan_merge_into_rejects_invalid_actions_and_structure(
);
}
+/// With the `DatetimeFunctionPlanner`, `AT TIME ZONE` plans as the `timezone`
+/// function for every input type. The function picks the result type after
+/// type coercion, so the SQL planner does not need the input type.
+#[test]
+fn plan_at_time_zone_with_datetime_planner() {
+ let state = MockSessionState::default()
+ .with_expr_planner(Arc::new(DatetimeFunctionPlanner))
+ .with_scalar_function(datetime::timezone());
+ let plan = logical_plan_from_state(
+ "SELECT birth_date AT TIME ZONE 'America/Denver' FROM person",
+ &GenericDialect {},
+ ParserOptions::default(),
+ state,
+ )
+ .unwrap();
+ assert_snapshot!(
+ plan,
+ @r#"
+ Projection: timezone(Utf8("America/Denver"), person.birth_date)
+ TableScan: person
+ "#
+ );
+}
+
+/// Without an `ExprPlanner` for `AT TIME ZONE`, planning fails for every input,
+/// as for `EXTRACT`. The SQL planner does not know the input type after type
+/// coercion, so it cannot choose a lowering on its own.
+#[test]
+fn plan_at_time_zone_without_planner() {
+ let err = logical_plan("SELECT birth_date AT TIME ZONE 'America/Denver' FROM person")
+ .unwrap_err();
+ assert!(
+ err.strip_backtrace()
+ .contains("AT TIME ZONE needs an ExprPlanner"),
+ "unexpected error: {err}"
+ );
+}
+
fn logical_plan(sql: &str) -> Result<LogicalPlan> {
logical_plan_with_options(sql, ParserOptions::default())
}
diff --git a/datafusion/sqllogictest/test_files/datetime/timestamps.slt b/datafusion/sqllogictest/test_files/datetime/timestamps.slt
index f52b8fb2ef..bc6a6d07ad 100644
--- a/datafusion/sqllogictest/test_files/datetime/timestamps.slt
+++ b/datafusion/sqllogictest/test_files/datetime/timestamps.slt
@@ -5037,6 +5037,133 @@ SELECT '2000-12-01 04:04:12' AT TIME ZONE 'America/New York';
statement error
SELECT '2023-03-12 02:00:00' AT TIME ZONE 'EDT';
+##########
+## AT TIME ZONE applied to a timezone-*aware* timestamp
+##
+## https://github.com/apache/datafusion/issues/12218
+##
+## PostgreSQL (and DuckDB) semantics are asymmetric:
+##
+## * `<tz-naive timestamp> AT TIME ZONE zone` reads the value as a wall clock
+## in `zone` and returns the matching tz-*aware* instant, and
+## * `<tz-aware timestamp> AT TIME ZONE zone` returns the wall clock that the
+## instant has in `zone`, as a tz-*naive* timestamp.
+##
+## Every result below was checked against PostgreSQL 17.
+##########
+
+statement ok
+SET datafusion.execution.time_zone = 'UTC';
+
+statement ok
+CREATE TABLE at_tz_t AS
+SELECT
+ '2024-01-01T12:00:00Z'::timestamptz AS tstz,
+ '2024-01-01 12:00:00'::timestamp AS tsn;
+
+# The tz-naive column is unaffected by this issue: noon-in-Denver is the same
+# instant PostgreSQL reports (`2024-01-01 19:00:00+00`).
+query TP
+SELECT arrow_typeof(tsn AT TIME ZONE 'America/Denver'), tsn AT TIME ZONE 'America/Denver' FROM at_tz_t;
+----
+Timestamp(ns, "America/Denver") 2024-01-01T12:00:00-07:00
+
+# The tz-aware column: PostgreSQL returns `timestamp` (naive) `2024-01-01 05:00:00`.
+query TP
+SELECT arrow_typeof(tstz AT TIME ZONE 'America/Denver'), tstz AT TIME ZONE 'America/Denver' FROM at_tz_t;
+----
+Timestamp(ns) 2024-01-01T05:00:00
+
+# The exact reproducer from #12218. PostgreSQL returns `2024-01-01 05:00:00`.
+query P
+SELECT (tstz AT TIME ZONE 'America/Denver')::timestamp FROM at_tz_t;
+----
+2024-01-01T05:00:00
+
+# Chained. PostgreSQL: `timestamptz` `2024-01-01 04:00:00+00`, i.e. the Denver
+# wall clock (05:00) re-read as a Brussels wall clock.
+query TP
+SELECT
+ arrow_typeof(tstz AT TIME ZONE 'America/Denver' AT TIME ZONE 'Europe/Brussels'),
+ tstz AT TIME ZONE 'America/Denver' AT TIME ZONE 'Europe/Brussels'
+FROM at_tz_t;
+----
+Timestamp(ns, "Europe/Brussels") 2024-01-01T05:00:00+01:00
+
+# Fixed offset. Note DataFusion follows the arrow/ISO-8601 sign convention here
+# (`+05:30` is 5h30m *east* of UTC), while PostgreSQL applies the POSIX
+# convention to offsets spelled as strings. That difference is pre-existing and
+# is not what #12218 is about; only the result *type* changes below.
+query TP
+SELECT arrow_typeof(tstz AT TIME ZONE '+05:30'), tstz AT TIME ZONE '+05:30' FROM at_tz_t;
+----
+Timestamp(ns) 2024-01-01T17:30:00
+
+# `AT TIME ZONE` preserves the precision of its input.
+query T
+SELECT arrow_typeof(arrow_cast('2024-01-01T12:00:00Z', 'Timestamp(Microsecond, Some("UTC"))') AT TIME ZONE 'America/Denver');
+----
+Timestamp(µs)
+
+query T
+SELECT arrow_typeof(arrow_cast('2024-01-01T12:00:00', 'Timestamp(Microsecond, None)') AT TIME ZONE 'America/Denver');
+----
+Timestamp(µs, "America/Denver")
+
+# A typed NULL takes the dispatch of its type. PostgreSQL:
+# `NULL::timestamp AT TIME ZONE zone` is a NULL `timestamptz`, and
+# `NULL::timestamptz AT TIME ZONE zone` is a NULL `timestamp`.
+query TP
+SELECT arrow_typeof(NULL::timestamp AT TIME ZONE 'America/Denver'), NULL::timestamp AT TIME ZONE 'America/Denver';
+----
+Timestamp(ns, "America/Denver") NULL
+
+query TP
+SELECT arrow_typeof(NULL::timestamptz AT TIME ZONE 'America/Denver'), NULL::timestamptz AT TIME ZONE 'America/Denver';
+----
+Timestamp(ns) NULL
+
+# A real (multi-row) tz-aware column, spanning both DST transitions in
+# America/Denver.
+statement ok
+CREATE TABLE at_tz_dst(ts timestamptz) AS VALUES
+ (arrow_cast('2024-01-01T12:00:00Z', 'Timestamp(Nanosecond, Some("UTC"))')),
+ (arrow_cast('2024-03-10T08:59:00Z', 'Timestamp(Nanosecond, Some("UTC"))')),
+ (arrow_cast('2024-03-10T09:00:00Z', 'Timestamp(Nanosecond, Some("UTC"))')),
+ (arrow_cast('2024-07-01T12:00:00Z', 'Timestamp(Nanosecond, Some("UTC"))')),
+ (arrow_cast('2024-11-03T07:59:00Z', 'Timestamp(Nanosecond, Some("UTC"))')),
+ (arrow_cast('2024-11-03T08:00:00Z', 'Timestamp(Nanosecond, Some("UTC"))'));
+
+query PPT
+SELECT
+ ts,
+ ts AT TIME ZONE 'America/Denver' AS denver,
+ arrow_typeof(ts AT TIME ZONE 'America/Denver') AS denver_type
+FROM at_tz_dst ORDER BY ts;
+----
+2024-01-01T12:00:00Z 2024-01-01T05:00:00 Timestamp(ns)
+2024-03-10T08:59:00Z 2024-03-10T01:59:00 Timestamp(ns)
+2024-03-10T09:00:00Z 2024-03-10T03:00:00 Timestamp(ns)
+2024-07-01T12:00:00Z 2024-07-01T06:00:00 Timestamp(ns)
+2024-11-03T07:59:00Z 2024-11-03T01:59:00 Timestamp(ns)
+2024-11-03T08:00:00Z 2024-11-03T01:00:00 Timestamp(ns)
+
+# `now()` is tz-aware whenever `datafusion.execution.time_zone` is set.
+query T
+SELECT arrow_typeof(now() AT TIME ZONE 'America/Denver');
+----
+Timestamp(ns)
+
+statement ok
+DROP TABLE at_tz_dst;
+
+statement ok
+DROP TABLE at_tz_t;
+
+statement ok
+RESET datafusion.execution.time_zone;
+
+
# Test current_time without parentheses
query B
select current_time = current_time;
@@ -5083,7 +5210,7 @@ statement error DataFusion error: Error during planning: Function 'to_local_time
select to_local_time('2024-04-01T00:00:20Z');
# invalid timezone
-statement error DataFusion error: Optimizer rule 'simplify_expressions' failed[\s\S]*Arrow error: Parser error: Invalid timezone "Europe/timezone": failed to parse timezone
+statement error DataFusion error: Arrow error: Parser error: Invalid timezone "Europe/timezone": failed to parse timezone
select to_local_time('2024-04-01T00:00:20Z'::timestamp AT TIME ZONE 'Europe/timezone');
# valid query
@@ -5286,10 +5413,14 @@ SELECT column1 FROM t_utc WHERE column1 < '2024-02-01T00:00:00' AT TIME ZONE 'Am
2024-01-01T00:00:01Z
2024-02-01T00:00:01Z
+# No row matches: the row `2024-02-01T00:00:01+01:00` is 2024-01-31T23:00:01Z,
+# and the literal is 2024-02-01T00:00:01Z. DuckDB also returns no row. Before
+# `AT TIME ZONE` became the `timezone` function, it was a `CAST`, and
+# `unwrap_cast_in_comparison` relabelled the literal instead of converting it
+# (https://github.com/apache/datafusion/issues/25095), so this row matched.
query P
SELECT column1 FROM t_europe WHERE column1 = '2024-01-31T16:00:01' AT TIME ZONE 'America/Los_Angeles';
----
-2024-02-01T00:00:01+01:00
query P
SELECT column1 FROM t_europe WHERE column1 BETWEEN '2020-01-01T00:00:00' AT TIME ZONE 'Australia/Brisbane' AND '2024-02-01T00:00:00' AT TIME ZONE 'America/Los_Angeles';
@@ -6517,3 +6648,162 @@ query P
SELECT date_bin(NULL, TIMESTAMP '2023-01-01 12:30:00', TIMESTAMP '2023-01-01 12:00:00')
----
NULL
+
+# `AT TIME ZONE` chooses its behaviour from the input type AFTER type
+# coercion. Each input below is `Timestamp(ns, "UTC")` once coerced, even when
+# the first CASE arm or the first UNION branch is naive. PostgreSQL 17 gives the
+# naive `2024-01-01 05:00:00` for all of them.
+statement ok
+CREATE TABLE at_tz_case AS
+SELECT arrow_cast('2024-01-01T12:00:00Z', 'Timestamp(Nanosecond, Some("UTC"))') AS aware,
+ arrow_cast('2024-01-01T12:00:00', 'Timestamp(Nanosecond, None)') AS naive,
+ arrow_cast('2024-01-01T12:00:00.123456789Z', 'Timestamp(Nanosecond, Some("UTC"))') AS aware_frac,
+ arrow_cast('2024-01-01T12:00:00.123456789', 'Timestamp(Nanosecond, None)') AS naive_frac,
+ arrow_cast('2024-01-01T12:00:00Z', 'Timestamp(Second, Some("UTC"))') AS aware_s,
+ arrow_cast('2024-01-01T12:00:00', 'Timestamp(Second, None)') AS naive_s,
+ true AS b
+
+# both CASE expressions have the same type
+query TT
+SELECT arrow_typeof(CASE WHEN b THEN naive ELSE aware END),
+ arrow_typeof(CASE WHEN b THEN aware ELSE naive END)
+FROM at_tz_case
+----
+Timestamp(ns, "UTC") Timestamp(ns, "UTC")
+
+# so AT TIME ZONE agrees on them, whatever the order of the arms
+query TPTP
+SELECT arrow_typeof((CASE WHEN b THEN naive ELSE aware END) AT TIME ZONE 'America/Denver'),
+ (CASE WHEN b THEN naive ELSE aware END) AT TIME ZONE 'America/Denver',
+ arrow_typeof((CASE WHEN b THEN aware ELSE naive END) AT TIME ZONE 'America/Denver'),
+ (CASE WHEN b THEN aware ELSE naive END) AT TIME ZONE 'America/Denver'
+FROM at_tz_case
+----
+Timestamp(ns) 2024-01-01T05:00:00 Timestamp(ns) 2024-01-01T05:00:00
+
+# CTE / derived-table column over such a CASE
+query TP
+WITH c AS (SELECT CASE WHEN b THEN naive ELSE aware END AS x FROM at_tz_case)
+SELECT arrow_typeof(x AT TIME ZONE 'America/Denver'), x AT TIME ZONE 'America/Denver' FROM c
+----
+Timestamp(ns) 2024-01-01T05:00:00
+
+# UNION ALL with the naive branch first
+query TP
+SELECT arrow_typeof(x AT TIME ZONE 'America/Denver'), x AT TIME ZONE 'America/Denver'
+FROM (SELECT naive AS x FROM at_tz_case UNION ALL SELECT aware FROM at_tz_case)
+----
+Timestamp(ns) 2024-01-01T05:00:00
+Timestamp(ns) 2024-01-01T05:00:00
+
+# aggregate over such a CASE
+query TP
+SELECT arrow_typeof(max(CASE WHEN b THEN naive ELSE aware END) AT TIME ZONE 'America/Denver'),
+ max(CASE WHEN b THEN naive ELSE aware END) AT TIME ZONE 'America/Denver'
+FROM at_tz_case
+----
+Timestamp(ns) 2024-01-01T05:00:00
+
+# The time unit also comes from the coerced type. Here the first arm or branch
+# has second precision, but the coerced type has nanosecond precision, so the
+# fraction of a second is kept.
+query TPTP
+SELECT arrow_typeof((CASE WHEN NOT b THEN naive_s ELSE aware_frac END) AT TIME ZONE 'UTC'),
+ (CASE WHEN NOT b THEN naive_s ELSE aware_frac END) AT TIME ZONE 'UTC',
+ arrow_typeof((CASE WHEN NOT b THEN aware_s ELSE naive_frac END) AT TIME ZONE 'UTC'),
+ (CASE WHEN NOT b THEN aware_s ELSE naive_frac END) AT TIME ZONE 'UTC'
+FROM at_tz_case
+----
+Timestamp(ns) 2024-01-01T12:00:00.123456789 Timestamp(ns) 2024-01-01T12:00:00.123456789
+
+query TP rowsort
+SELECT arrow_typeof(x AT TIME ZONE 'UTC'), x AT TIME ZONE 'UTC'
+FROM (SELECT naive_s AS x FROM at_tz_case UNION ALL SELECT naive_frac FROM at_tz_case)
+----
+Timestamp(ns, "UTC") 2024-01-01T12:00:00.123456789Z
+Timestamp(ns, "UTC") 2024-01-01T12:00:00Z
+
+# A dictionary-encoded timestamp follows its value type
+query TP
+SELECT arrow_typeof(d AT TIME ZONE 'America/Denver'), d AT TIME ZONE 'America/Denver'
+FROM (SELECT arrow_cast(aware, 'Dictionary(Int32, Timestamp(Nanosecond, Some("UTC")))') AS d FROM at_tz_case)
+----
+Timestamp(ns) 2024-01-01T05:00:00
+
+query TP
+SELECT arrow_typeof(d AT TIME ZONE 'America/Denver'), d AT TIME ZONE 'America/Denver'
+FROM (SELECT arrow_cast(naive, 'Dictionary(Int32, Timestamp(Nanosecond, None))') AS d FROM at_tz_case)
+----
+Timestamp(ns, "America/Denver") 2024-01-01T12:00:00-07:00
+
+# `PREPARE` runs the optimizer before type coercion, and an untyped placeholder
+# has no type until `EXECUTE`. The branch is still chosen from the final type.
+statement ok
+PREPARE at_tz_p AS SELECT arrow_typeof($1 AT TIME ZONE 'America/Denver'), $1 AT TIME ZONE 'America/Denver'
+
+query TP
+EXECUTE at_tz_p(arrow_cast('2024-01-01T12:00:00Z', 'Timestamp(Nanosecond, Some("UTC"))'))
+----
+Timestamp(ns) 2024-01-01T05:00:00
+
+query TP
+EXECUTE at_tz_p(arrow_cast('2024-01-01T12:00:00', 'Timestamp(Nanosecond, None)'))
+----
+Timestamp(ns, "America/Denver") 2024-01-01T12:00:00-07:00
+
+statement ok
+DEALLOCATE at_tz_p
+
+statement ok
+PREPARE at_tz_case_p(boolean) AS
+SELECT arrow_typeof((CASE WHEN b THEN naive ELSE aware END) AT TIME ZONE 'America/Denver'),
+ (CASE WHEN b THEN naive ELSE aware END) AT TIME ZONE 'America/Denver'
+FROM at_tz_case WHERE b = $1
+
+query TP
+EXECUTE at_tz_case_p(true)
+----
+Timestamp(ns) 2024-01-01T05:00:00
+
+statement ok
+DEALLOCATE at_tz_case_p
+
+# A view over such a CASE
+statement ok
+CREATE VIEW at_tz_case_v AS SELECT CASE WHEN b THEN naive ELSE aware END AS x FROM at_tz_case
+
+query TP
+SELECT arrow_typeof(x AT TIME ZONE 'America/Denver'), x AT TIME ZONE 'America/Denver' FROM at_tz_case_v
+----
+Timestamp(ns) 2024-01-01T05:00:00
+
+statement ok
+DROP VIEW at_tz_case_v
+
+# `AT TIME ZONE` stays a `timezone` call in the plan. It is not rewritten into
+# a `CAST` during planning, because the type at that point is not always final.
+# The column is named with the operator syntax.
+query TT
+EXPLAIN SELECT (CASE WHEN b THEN naive ELSE aware END) AT TIME ZONE 'America/Denver', naive AT TIME ZONE 'America/Denver' FROM at_tz_case
+----
+logical_plan
+01)Projection: timezone(Utf8("America/Denver"), CASE WHEN at_tz_case.b THEN CAST(at_tz_case.naive AS Timestamp(ns, "UTC")) ELSE at_tz_case.aware END), timezone(Utf8("America/Denver"), at_tz_case.naive)
+02)--TableScan: at_tz_case projection=[aware, naive, b]
+physical_plan
+01)ProjectionExec: expr=[timezone(America/Denver, CASE WHEN b@2 THEN CAST(naive@1 AS Timestamp(ns, "UTC")) ELSE aware@0 END) as CASE WHEN at_tz_case.b THEN at_tz_case.naive ELSE at_tz_case.aware END AT TIME ZONE 'America/Denver', timezone(America/Denver, naive@1) as at_tz_case.naive AT TIME ZONE 'America/Denver']
+02)--DataSourceExec: partitions=1, partition_sizes=[1]
+
+# `timezone(zone, expression)` is the function form, as in PostgreSQL and DuckDB
+query TPTP
+SELECT arrow_typeof(timezone('America/Denver', aware)), timezone('America/Denver', aware),
+ arrow_typeof(timezone('America/Denver', naive)), timezone('America/Denver', naive)
+FROM at_tz_case
+----
+Timestamp(ns) 2024-01-01T05:00:00 Timestamp(ns, "America/Denver") 2024-01-01T12:00:00-07:00
+
+# The zone decides the result type, so it must be a constant
+statement error timezone requires its first argument \(the timezone\) to be a constant, non-empty string
+SELECT timezone(arrow_cast(b, 'Utf8'), aware) FROM at_tz_case
+
+statement ok
+DROP TABLE at_tz_case
diff --git a/datafusion/sqllogictest/test_files/datetime/timestamps_timezone.slt b/datafusion/sqllogictest/test_files/datetime/timestamps_timezone.slt
index 42ae7c6b53..90b553551d 100644
--- a/datafusion/sqllogictest/test_files/datetime/timestamps_timezone.slt
+++ b/datafusion/sqllogictest/test_files/datetime/timestamps_timezone.slt
@@ -311,41 +311,35 @@ Timestamp(ns, "+05:30") 2024-07-01T12:00:00+05:30
# 5b. AT TIME ZONE applied to a tz-AWARE value.
#
-# DIVERGES FROM POSTGRESQL (type, not instant). PostgreSQL's
-# `timestamptz AT TIME ZONE zone` *drops* the zone and returns
-# `timestamp without time zone` holding the wall clock reading in `zone`: for
-# the first query below PostgreSQL returns the naive values
-# `2024-01-15 13:00:00` / `2024-07-01 14:00:00`. DataFusion keeps the value
-# tz-aware and merely relabels which zone it is rendered in, so the instant is
-# preserved but the type is not what a PostgreSQL user expects, and a
-# subsequent cast or comparison behaves differently as a result.
-# See https://github.com/apache/datafusion/issues/12218
+# Matches PostgreSQL: `timestamptz AT TIME ZONE zone` drops the zone and
+# returns `timestamp without time zone` holding the wall clock reading in
+# `zone`. For the first query below PostgreSQL returns the naive values
+# `2024-01-15 13:00:00` / `2024-07-01 14:00:00`.
query TP rowsort
SELECT arrow_typeof(column1 AT TIME ZONE 'Europe/Brussels'),
column1 AT TIME ZONE 'Europe/Brussels'
FROM c_utc
----
-Timestamp(ns, "Europe/Brussels") 2024-01-15T13:00:00+01:00
-Timestamp(ns, "Europe/Brussels") 2024-07-01T14:00:00+02:00
+Timestamp(ns) 2024-01-15T13:00:00
+Timestamp(ns) 2024-07-01T14:00:00
query TP rowsort
SELECT arrow_typeof(column1 AT TIME ZONE 'America/Denver'),
column1 AT TIME ZONE 'America/Denver'
FROM c_denver
----
-Timestamp(ns, "America/Denver") 2024-01-15T12:00:00-07:00
-Timestamp(ns, "America/Denver") 2024-07-01T12:00:00-06:00
+Timestamp(ns) 2024-01-15T12:00:00
+Timestamp(ns) 2024-07-01T12:00:00
# KNOWN WRONG. The composed idiom `tstz AT TIME ZONE zone` followed by a cast
# to naive is the standard PostgreSQL way to read a wall clock in a named zone.
# PostgreSQL (TimeZone='UTC') answers `2024-07-01 12:00:00`. DataFusion with
# the session zone unset answers `2024-07-02T00:00:00` -- six hours *later*
# than the input instant rather than six hours earlier, because the literal is
-# naive (SECTION 0), `AT TIME ZONE` then relabels rather than converts it, and
-# `::timestamp` finally renders the result back in UTC. Three separate
-# behaviours from this file compose into an answer that is a whole day out.
-# See https://github.com/apache/datafusion/issues/12218 and
-# https://github.com/apache/datafusion/issues/25166
+# naive (SECTION 0), so `AT TIME ZONE` takes the naive path and reads 18:00 as
+# a Denver wall clock, and `::timestamp` then renders the result in UTC. With a
+# tz-aware input the query answers correctly (SECTION 5b).
+# See https://github.com/apache/datafusion/issues/25166
query P
SELECT ('2024-07-01T18:00:00Z'::timestamptz AT TIME ZONE 'America/Denver')::timestamp
----
diff --git a/docs/source/user-guide/sql/operators.md b/docs/source/user-guide/sql/operators.md
index de706cc21f..688ac974f6 100644
--- a/docs/source/user-guide/sql/operators.md
+++ b/docs/source/user-guide/sql/operators.md
@@ -507,6 +507,7 @@ Bitwise Shift Left
- [|| (string concatenation)](#op_str_cat)
- [@> (array contains)](#op_arr_contains)
- [<@ (array is contained by)](#op_arr_contained_by)
+- [AT TIME ZONE](#op_at_time_zone)
(op_str_cat)=
@@ -553,6 +554,69 @@ Array Is Contained By
+-------------------------------------------------------------------------+
```
+(op_at_time_zone)=
+
+### `AT TIME ZONE`
+
+Converts between timezone-naive and timezone-aware timestamps, following
+PostgreSQL. `AT TIME ZONE` always returns the _other_ kind of timestamp from
+the one it is given:
+
+| Input type | Result type | Meaning |
+| ---------------------------------------- | ---------------------------------------- | ------------------------------------------------------------------- |
+| `Timestamp(unit, None)` (timezone-naive) | `Timestamp(unit, Some(tz))` (tz-aware) | Read the value as a wall clock in `tz`; the result is that instant. |
+| `Timestamp(unit, Some(_))` (tz-aware) | `Timestamp(unit, None)` (timezone-naive) | Return the wall clock that instant has in `tz`. |
+
+A timezone-naive timestamp is a wall clock with no instant attached, so
+`AT TIME ZONE` pins it to one:
+
+```sql
+> SET datafusion.execution.time_zone = 'UTC';
+> SELECT
+ '2024-01-01 12:00:00'::timestamp AT TIME ZONE 'America/Denver' AS instant,
+ arrow_typeof('2024-01-01 12:00:00'::timestamp AT TIME ZONE 'America/Denver') AS type;
++---------------------------+---------------------------------+
+| instant | type |
++---------------------------+---------------------------------+
+| 2024-01-01T12:00:00-07:00 | Timestamp(ns, "America/Denver") |
++---------------------------+---------------------------------+
+```
+
+A timezone-aware timestamp already is an instant, so `AT TIME ZONE` reads off
+its wall clock in `tz` and drops the timezone. Daylight saving time is taken
+into account:
+
+```sql
+> SELECT
+ '2024-01-01T12:00:00Z'::timestamptz AT TIME ZONE 'America/Denver' AS wall_clock,
+ arrow_typeof('2024-01-01T12:00:00Z'::timestamptz AT TIME ZONE 'America/Denver') AS type;
++---------------------+---------------+
+| wall_clock | type |
++---------------------+---------------+
+| 2024-01-01T05:00:00 | Timestamp(ns) |
++---------------------+---------------+
+```
+
+`expression AT TIME ZONE tz` is the same as the function call
+[`timezone(tz, expression)`], as in PostgreSQL and DuckDB. The planner uses the
+input type _after_ type coercion. For example, `CASE WHEN c THEN naive ELSE aware END` has a timezone-aware type, so `AT TIME ZONE` returns a
+timezone-naive timestamp for it, whatever the order of the arms.
+
+Because the two forms return different types, applying `AT TIME ZONE` twice
+returns a timezone-aware timestamp again: the wall clock produced by the first
+application is re-read as a local time in the second timezone.
+
+`AT TIME ZONE` never changes the precision (`TimeUnit`) of its input. A
+dictionary-encoded timestamp follows its value type. Inputs that are not
+timestamps at all (a string literal, for example) are cast to
+`Timestamp(Nanosecond, Some(tz))`.
+
+Note that a timezone written as a fixed offset string follows the ISO 8601
+convention, so `'+05:30'` is 5 hours 30 minutes _east_ of UTC. PostgreSQL
+applies the opposite (POSIX) convention to offsets spelled this way.
+
+[`timezone(tz, expression)`]: scalar_functions.md#timezone
+
## Literals
Use single quotes for literal values. For example, the string `foo bar` is
diff --git a/docs/source/user-guide/sql/scalar_functions.md b/docs/source/user-guide/sql/scalar_functions.md
index 986aa44488..8b7cfffe1b 100644
--- a/docs/source/user-guide/sql/scalar_functions.md
+++ b/docs/source/user-guide/sql/scalar_functions.md
@@ -2384,6 +2384,7 @@ Additional examples can be found [here](https://github.com/apache/datafusion/blo
- [make_date](#make_date)
- [make_time](#make_time)
- [now](#now)
+- [timezone](#timezone)
- [to_char](#to_char)
- [to_date](#to_date)
- [to_local_time](#to_local_time)
@@ -2807,6 +2808,44 @@ now()
- current_timestamp
+### `timezone`
+
+Converts a timestamp to another timezone. This is the function form of `expression AT TIME ZONE zone`, and it follows PostgreSQL and DuckDB:
+
+- A timestamp **with** a timezone is an instant. The result is the wall-clock time of that instant in `zone`, as a timestamp **without** a timezone.
+- A timestamp **without** a timezone is a wall-clock time. The result is the instant at which that wall-clock time occurs in `zone`, as a timestamp **with** the timezone `zone`.
+- Any other type (for example a string) is converted as for a timestamp without a timezone.
+
+The result keeps the time unit of a timestamp input.
+
+```sql
+timezone(zone, expression)
+```
+
+#### Arguments
+
+- **zone**: Target timezone, as a constant string. An IANA timezone name such as `'America/Denver'`, or a fixed offset such as `'+05:30'`.
+- **expression**: Timestamp expression to convert. Can be a constant, column, or function.
+
+#### Example
+
+```sql
+> SET datafusion.execution.time_zone = 'UTC';
+> SELECT timezone('America/Denver', '2024-01-01T12:00:00Z'::timestamptz) AS denver_wall_clock;
++---------------------+
+| denver_wall_clock |
++---------------------+
+| 2024-01-01T05:00:00 |
++---------------------+
+
+> SELECT timezone('America/Denver', '2024-01-01T12:00:00'::timestamp) AS denver_instant;
++---------------------------+
+| denver_instant |
++---------------------------+
+| 2024-01-01T12:00:00-07:00 |
++---------------------------+
+```
+
### `to_char`
Returns a string representation of a date, time, timestamp or duration based on a [Chrono format](https://docs.rs/chrono/latest/chrono/format/strftime/index.html). Unlike the PostgreSQL equivalent of this function numerical formatting is not supported.
@@ -2881,6 +2920,10 @@ Additional examples can be found [here](https://github.com/apache/datafusion/blo
Converts a timestamp with a timezone to a timestamp without a timezone (with no offset or timezone information). This function handles daylight saving time changes.
+A timestamp that already has no timezone is returned unchanged.
+
+`to_local_time` uses the timezone that the value already has. To get the wall-clock time in a timezone that you name in the query, use [`timezone(zone, expression)`](#timezone) or `expression AT TIME ZONE zone`: for a timestamp with a timezone, both return the wall-clock time in `zone`, without a timezone.
+
```sql
to_local_time(expression)
```
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@datafusion.apache.org
For additional commands, e-mail: commits-help@datafusion.apache.org