|
| 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 | +} |
0 commit comments