Skip to content

Commit e0c60b5

Browse files
adriangbclaude
andcommitted
feat: add timezone(zone, expression), the function form of AT TIME ZONE
PostgreSQL, DuckDB and Redshift all define `expression AT TIME ZONE zone` as `timezone(zone, expression)`. The function picks its result type from the input type after type coercion, and `simplify` lowers it to the same `CAST` / `to_local_time(CAST)` shape that the SQL planner emits today. Co-Authored-By: Claude Opus 5.5 <[email protected]>
1 parent fd1c716 commit e0c60b5

4 files changed

Lines changed: 329 additions & 3 deletions

File tree

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

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -32,6 +32,7 @@ pub mod make_date;
3232
pub mod make_time;
3333
pub mod now;
3434
pub mod planner;
35+
pub mod timezone;
3536
pub mod to_char;
3637
pub mod to_date;
3738
pub mod to_local_time;
@@ -47,6 +48,7 @@ make_udf_function!(date_part::DatePartFunc, date_part);
4748
make_udf_function!(date_trunc::DateTruncFunc, date_trunc);
4849
make_udf_function!(make_date::MakeDateFunc, make_date);
4950
make_udf_function!(make_time::MakeTimeFunc, make_time);
51+
make_udf_function!(timezone::TimezoneFunc, timezone);
5052
make_udf_function!(to_char::ToCharFunc, to_char);
5153
make_udf_function!(to_date::ToDateFunc, to_date);
5254
make_udf_function!(to_local_time::ToLocalTimeFunc, to_local_time);
@@ -285,6 +287,7 @@ pub fn functions() -> Vec<Arc<ScalarUDF>> {
285287
make_date(),
286288
make_time(),
287289
now(&config),
290+
timezone(),
288291
to_char(),
289292
to_date(),
290293
to_local_time(),
Lines changed: 284 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,284 @@
1+
// Licensed to the Apache Software Foundation (ASF) under one
2+
// or more contributor license agreements. See the NOTICE file
3+
// distributed with this work for additional information
4+
// regarding copyright ownership. The ASF licenses this file
5+
// to you under the Apache License, Version 2.0 (the
6+
// "License"); you may not use this file except in compliance
7+
// with the License. You may obtain a copy of the License at
8+
//
9+
// http://www.apache.org/licenses/LICENSE-2.0
10+
//
11+
// Unless required by applicable law or agreed to in writing,
12+
// software distributed under the License is distributed on an
13+
// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
14+
// KIND, either express or implied. See the License for the
15+
// specific language governing permissions and limitations
16+
// under the License.
17+
18+
use std::sync::Arc;
19+
20+
use arrow::datatypes::{DataType, Field, FieldRef, TimeUnit};
21+
use datafusion_common::types::logical_string;
22+
use datafusion_common::utils::take_function_args;
23+
use datafusion_common::{Result, ScalarValue, exec_err, internal_err};
24+
use datafusion_expr::expr::ScalarFunction;
25+
use datafusion_expr::simplify::{ExprSimplifyResult, SimplifyContext};
26+
use datafusion_expr::{
27+
Cast, Coercion, ColumnarValue, Documentation, Expr, ReturnFieldArgs,
28+
ScalarFunctionArgs, ScalarUDFImpl, Signature, TypeSignatureClass, Volatility,
29+
};
30+
use datafusion_macros::user_doc;
31+
32+
/// `timezone(zone, expression)`: the function form of the SQL operator
33+
/// `expression AT TIME ZONE zone`, with the semantics of PostgreSQL and DuckDB.
34+
///
35+
/// The operator always returns the *other* kind of timestamp:
36+
///
37+
/// | Input type (after coercion) | Result | Plan after simplification |
38+
/// | --- | --- | --- |
39+
/// | `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))))` |
40+
/// | `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)))` |
41+
/// | any other type | as for a naive input | `CAST(expression AS Timestamp(ns, Some(zone)))` |
42+
///
43+
/// The choice needs the input type *after* type coercion. For example, a
44+
/// `CASE` with a naive arm and an aware arm is aware only after coercion. The
45+
/// SQL planner runs before coercion, so it plans `AT TIME ZONE` as a call to
46+
/// this function (see `DatetimeFunctionPlanner`), and [`Self::simplify`]
47+
/// lowers the call once the type is known.
48+
///
49+
/// `zone` must be a constant, because it determines the result type.
50+
#[user_doc(
51+
doc_section(label = "Time and Date Functions"),
52+
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:
53+
54+
- 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.
55+
- 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`.
56+
- Any other type (for example a string) is converted as for a timestamp without a timezone.
57+
58+
The result keeps the time unit of a timestamp input."#,
59+
syntax_example = "timezone(zone, expression)",
60+
sql_example = r#"```sql
61+
> SET datafusion.execution.time_zone = 'UTC';
62+
> SELECT timezone('America/Denver', '2024-01-01T12:00:00Z'::timestamptz) AS denver_wall_clock;
63+
+---------------------+
64+
| denver_wall_clock |
65+
+---------------------+
66+
| 2024-01-01T05:00:00 |
67+
+---------------------+
68+
69+
> SELECT timezone('America/Denver', '2024-01-01T12:00:00'::timestamp) AS denver_instant;
70+
+---------------------------+
71+
| denver_instant |
72+
+---------------------------+
73+
| 2024-01-01T12:00:00-07:00 |
74+
+---------------------------+
75+
```"#,
76+
argument(
77+
name = "zone",
78+
description = "Target timezone, as a constant string. An IANA timezone name such as `'America/Denver'`, or a fixed offset such as `'+05:30'`."
79+
),
80+
argument(
81+
name = "expression",
82+
description = "Timestamp expression to convert. Can be a constant, column, or function."
83+
)
84+
)]
85+
#[derive(Debug, PartialEq, Eq, Hash)]
86+
pub struct TimezoneFunc {
87+
signature: Signature,
88+
}
89+
90+
impl Default for TimezoneFunc {
91+
fn default() -> Self {
92+
Self::new()
93+
}
94+
}
95+
96+
impl TimezoneFunc {
97+
pub fn new() -> Self {
98+
Self {
99+
signature: Signature::coercible(
100+
vec![
101+
Coercion::new_exact(TypeSignatureClass::Native(logical_string())),
102+
Coercion::new_exact(TypeSignatureClass::Any),
103+
],
104+
Volatility::Immutable,
105+
),
106+
}
107+
}
108+
}
109+
110+
/// The two steps that `timezone(zone, <input_type>)` lowers to: the target
111+
/// type of the `CAST`, and whether `to_local_time` follows it.
112+
fn lowering(input_type: &DataType, zone: &str) -> (DataType, bool) {
113+
let (unit, input_is_aware) = match input_type {
114+
DataType::Timestamp(unit, tz) => (*unit, tz.is_some()),
115+
DataType::Dictionary(_, value_type) => match value_type.as_ref() {
116+
DataType::Timestamp(unit, tz) => (*unit, tz.is_some()),
117+
_ => (TimeUnit::Nanosecond, false),
118+
},
119+
_ => (TimeUnit::Nanosecond, false),
120+
};
121+
(DataType::Timestamp(unit, Some(zone.into())), input_is_aware)
122+
}
123+
124+
fn zone_from_scalar(name: &str, zone: Option<&ScalarValue>) -> Result<String> {
125+
match zone.and_then(|s| s.try_as_str().flatten()) {
126+
Some(s) if !s.is_empty() => Ok(s.to_string()),
127+
_ => exec_err!(
128+
"{name} requires its first argument (the timezone) to be a constant, non-empty string"
129+
),
130+
}
131+
}
132+
133+
impl ScalarUDFImpl for TimezoneFunc {
134+
fn name(&self) -> &str {
135+
"timezone"
136+
}
137+
138+
/// Name the column with the SQL operator syntax, so that
139+
/// `x AT TIME ZONE 'tz'` names its column `x AT TIME ZONE 'tz'`.
140+
fn schema_name(&self, args: &[Expr]) -> Result<String> {
141+
let [zone, input] = take_function_args(self.name(), args)?;
142+
let zone = match zone {
143+
Expr::Literal(sv, _) => match sv.try_as_str().flatten() {
144+
Some(s) => format!("'{s}'"),
145+
None => zone.schema_name().to_string(),
146+
},
147+
_ => zone.schema_name().to_string(),
148+
};
149+
Ok(format!("{} AT TIME ZONE {zone}", input.schema_name()))
150+
}
151+
152+
fn signature(&self) -> &Signature {
153+
&self.signature
154+
}
155+
156+
fn return_type(&self, _arg_types: &[DataType]) -> Result<DataType> {
157+
internal_err!("return_field_from_args should be called instead")
158+
}
159+
160+
fn return_field_from_args(&self, args: ReturnFieldArgs) -> Result<FieldRef> {
161+
let [_, input] = take_function_args(self.name(), args.arg_fields)?;
162+
let zone = zone_from_scalar(self.name(), args.scalar_arguments[0])?;
163+
let (cast_type, strip_timezone) = lowering(input.data_type(), &zone);
164+
let return_type = match cast_type {
165+
DataType::Timestamp(unit, _) if strip_timezone => {
166+
DataType::Timestamp(unit, None)
167+
}
168+
other => other,
169+
};
170+
Ok(Arc::new(Field::new(
171+
self.name(),
172+
return_type,
173+
input.is_nullable(),
174+
)))
175+
}
176+
177+
/// Lower to `CAST`, or to `to_local_time(CAST)`, now that the input type
178+
/// is the coerced one.
179+
fn simplify(
180+
&self,
181+
args: Vec<Expr>,
182+
info: &SimplifyContext,
183+
) -> Result<ExprSimplifyResult> {
184+
let [zone_arg, input] = take_function_args(self.name(), args)?;
185+
let Expr::Literal(zone, _) = &zone_arg else {
186+
return Ok(ExprSimplifyResult::Original(vec![zone_arg, input]));
187+
};
188+
let zone = zone_from_scalar(self.name(), Some(zone))?;
189+
let (cast_type, strip_timezone) = lowering(&info.get_data_type(&input)?, &zone);
190+
let cast = Expr::Cast(Cast::new(Box::new(input), cast_type));
191+
Ok(ExprSimplifyResult::Simplified(if strip_timezone {
192+
Expr::ScalarFunction(ScalarFunction::new_udf(
193+
super::to_local_time(),
194+
vec![cast],
195+
))
196+
} else {
197+
cast
198+
}))
199+
}
200+
201+
/// Reached only when the plan was not simplified.
202+
fn invoke_with_args(&self, args: ScalarFunctionArgs) -> Result<ColumnarValue> {
203+
let [zone, input] = take_function_args(self.name(), &args.args)?;
204+
let zone = match zone {
205+
ColumnarValue::Scalar(zone) => zone_from_scalar(self.name(), Some(zone))?,
206+
ColumnarValue::Array(_) => zone_from_scalar(self.name(), None)?,
207+
};
208+
let (cast_type, strip_timezone) = lowering(&input.data_type(), &zone);
209+
let cast = input.cast_to(&cast_type, None)?;
210+
if strip_timezone {
211+
super::to_local_time::to_local_time(&cast)
212+
} else {
213+
Ok(cast)
214+
}
215+
}
216+
217+
fn documentation(&self) -> Option<&Documentation> {
218+
self.doc()
219+
}
220+
}
221+
222+
#[cfg(test)]
223+
mod tests {
224+
use std::sync::Arc;
225+
226+
use arrow::array::{Array, ArrayRef, TimestampMicrosecondArray};
227+
use arrow::datatypes::{DataType, Field, TimeUnit};
228+
use datafusion_common::ScalarValue;
229+
use datafusion_common::config::ConfigOptions;
230+
use datafusion_expr::{ColumnarValue, ScalarFunctionArgs, ScalarUDFImpl};
231+
232+
use super::TimezoneFunc;
233+
234+
/// `invoke_with_args` must agree with the lowering that `simplify` does,
235+
/// for a plan that the simplifier did not visit.
236+
#[test]
237+
fn invoke_without_simplify() {
238+
// 2024-01-01T12:00:00Z
239+
let micros = 1_704_110_400_000_000;
240+
let cases = [
241+
// aware input -> naive wall clock in Denver (05:00)
242+
(
243+
Some("UTC"),
244+
DataType::Timestamp(TimeUnit::Microsecond, None),
245+
micros - 7 * 3_600_000_000,
246+
),
247+
// naive input -> the instant of 12:00 in Denver (19:00 UTC)
248+
(
249+
None,
250+
DataType::Timestamp(TimeUnit::Microsecond, Some("America/Denver".into())),
251+
micros + 7 * 3_600_000_000,
252+
),
253+
];
254+
for (input_tz, expected_type, expected_value) in cases {
255+
let input: ArrayRef = Arc::new(
256+
TimestampMicrosecondArray::from(vec![micros]).with_timezone_opt(input_tz),
257+
);
258+
let args = ScalarFunctionArgs {
259+
args: vec![
260+
ColumnarValue::Scalar(ScalarValue::from("America/Denver")),
261+
ColumnarValue::Array(Arc::clone(&input)),
262+
],
263+
arg_fields: vec![
264+
Arc::new(Field::new("zone", DataType::Utf8, false)),
265+
Arc::new(Field::new("input", input.data_type().clone(), false)),
266+
],
267+
number_rows: 1,
268+
return_field: Arc::new(Field::new("f", expected_type.clone(), false)),
269+
config_options: Arc::new(ConfigOptions::default()),
270+
};
271+
let ColumnarValue::Array(result) =
272+
TimezoneFunc::new().invoke_with_args(args).unwrap()
273+
else {
274+
panic!("expected an array");
275+
};
276+
assert_eq!(result.data_type(), &expected_type);
277+
let result = result
278+
.as_any()
279+
.downcast_ref::<TimestampMicrosecondArray>()
280+
.unwrap();
281+
assert_eq!(result.value(0), expected_value);
282+
}
283+
}
284+
}

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

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -48,7 +48,7 @@ use datafusion_macros::user_doc;
4848
4949
A timestamp that already has no timezone is returned unchanged.
5050
51-
`AT TIME ZONE` applied to a timezone-*aware* timestamp is defined in terms of this function: it relabels the instant into the target timezone and then applies `to_local_time`. Wrapping such an expression in `to_local_time` is therefore redundant. The examples below instead apply `AT TIME ZONE` to timezone-*naive* values, which is the form that produces a timezone-aware timestamp for `to_local_time` to strip."#,
51+
`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."#,
5252
syntax_example = "to_local_time(expression)",
5353
sql_example = r#"```sql
5454
> SELECT to_local_time('2024-04-01T00:00:20Z'::timestamp);
@@ -162,7 +162,7 @@ fn transform_array<T: ArrowTimestampType>(
162162
Ok(ColumnarValue::Array(Arc::new(result)))
163163
}
164164

165-
fn to_local_time(time_value: &ColumnarValue) -> Result<ColumnarValue> {
165+
pub(crate) fn to_local_time(time_value: &ColumnarValue) -> Result<ColumnarValue> {
166166
let arg_type = time_value.data_type();
167167

168168
let tz: Tz = match &arg_type {

‎docs/source/user-guide/sql/scalar_functions.md‎

Lines changed: 40 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -2384,6 +2384,7 @@ Additional examples can be found [here](https://github.com/apache/datafusion/blo
23842384
- [make_date](#make_date)
23852385
- [make_time](#make_time)
23862386
- [now](#now)
2387+
- [timezone](#timezone)
23872388
- [to_char](#to_char)
23882389
- [to_date](#to_date)
23892390
- [to_local_time](#to_local_time)
@@ -2807,6 +2808,44 @@ now()
28072808

28082809
- current_timestamp
28092810

2811+
### `timezone`
2812+
2813+
Converts a timestamp to another timezone. This is the function form of `expression AT TIME ZONE zone`, and it follows PostgreSQL and DuckDB:
2814+
2815+
- 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.
2816+
- 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`.
2817+
- Any other type (for example a string) is converted as for a timestamp without a timezone.
2818+
2819+
The result keeps the time unit of a timestamp input.
2820+
2821+
```sql
2822+
timezone(zone, expression)
2823+
```
2824+
2825+
#### Arguments
2826+
2827+
- **zone**: Target timezone, as a constant string. An IANA timezone name such as `'America/Denver'`, or a fixed offset such as `'+05:30'`.
2828+
- **expression**: Timestamp expression to convert. Can be a constant, column, or function.
2829+
2830+
#### Example
2831+
2832+
```sql
2833+
> SET datafusion.execution.time_zone = 'UTC';
2834+
> SELECT timezone('America/Denver', '2024-01-01T12:00:00Z'::timestamptz) AS denver_wall_clock;
2835+
+---------------------+
2836+
| denver_wall_clock |
2837+
+---------------------+
2838+
| 2024-01-01T05:00:00 |
2839+
+---------------------+
2840+
2841+
> SELECT timezone('America/Denver', '2024-01-01T12:00:00'::timestamp) AS denver_instant;
2842+
+---------------------------+
2843+
| denver_instant |
2844+
+---------------------------+
2845+
| 2024-01-01T12:00:00-07:00 |
2846+
+---------------------------+
2847+
```
2848+
28102849
### `to_char`
28112850

28122851
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.
@@ -2883,7 +2922,7 @@ Converts a timestamp with a timezone to a timestamp without a timezone (with no
28832922

28842923
A timestamp that already has no timezone is returned unchanged.
28852924

2886-
`AT TIME ZONE` applied to a timezone-_aware_ timestamp is defined in terms of this function: it relabels the instant into the target timezone and then applies `to_local_time`. Wrapping such an expression in `to_local_time` is therefore redundant. The examples below instead apply `AT TIME ZONE` to timezone-_naive_ values, which is the form that produces a timezone-aware timestamp for `to_local_time` to strip.
2925+
`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.
28872926

28882927
```sql
28892928
to_local_time(expression)

0 commit comments

Comments
 (0)