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-25161-b239d041b5430efa87755e4f42a3726da2db45a8 in repository https://gitbox.apache.org/repos/asf/datafusion.git
commit f8cc678272c50c781a56b74b49912b999aebd89e Author: Adrian Garcia Badaracco <[email protected]> AuthorDate: Fri Sep 11 15:59:58 2026 +0000 fix: `from_unixtime` should respect `datafusion.execution.time_zone` (#25161) ## Which issue does this PR close? - Closes https://github.com/apache/datafusion/issues/12892 ## Rationale for this change ### The bug, in SQL Set a session time zone. Every other date and time function honours it. `from_unixtime` does not. ```sql SET datafusion.execution.time_zone = 'America/Denver'; -- now() and to_timestamp() honour the session time zone SELECT arrow_typeof(now()), arrow_typeof(to_timestamp(1704110400)); -- Timestamp(ns, "America/Denver") | Timestamp(ns, "America/Denver") -- from_unixtime() does not SELECT arrow_typeof(from_unixtime(1704110400)), from_unixtime(1704110400); -- Timestamp(s) | 2024-01-01T12:00:00 ``` The last row is the defect. The user asks for a Denver session, but the value comes back as a bare wall clock with no zone. `2024-01-01T12:00:00` is the UTC clock, not the Denver clock. After this PR the same query returns the session time zone: ```sql SET datafusion.execution.time_zone = 'America/Denver'; SELECT arrow_typeof(from_unixtime(1704110400)), from_unixtime(1704110400); -- Timestamp(s, "America/Denver") | 2024-01-01T05:00:00-07:00 ``` The instant is the same. Only the declared type and the rendered offset change. ### What changes for a user - `from_unixtime(expr)` returns a value in `datafusion.execution.time_zone`. - `from_unixtime(expr, 'tz')` is unchanged. An explicit argument still wins. - With the session time zone unset, which is the default, nothing changes at all. - A value that no time zone can render is now an error, not a process panic. ### Field research: PostgreSQL and DuckDB Neither engine has a function named `from_unixtime`. The closest equivalent in both is `to_timestamp(<epoch>)`, so that is the comparison I ran. Both engines are timezone-aware there, and both render in the session time zone. **PostgreSQL 17.11** ```sql SHOW TimeZone; -- Etc/UTC SELECT to_timestamp(1704110400), pg_typeof(to_timestamp(1704110400)); -- 2024-01-01 12:00:00+00 | timestamp with time zone SET TimeZone = 'America/Denver'; SELECT to_timestamp(1704110400); -- 2024-01-01 05:00:00-07 SELECT extract(epoch from to_timestamp(1704110400)); -- 1704110400.000000 ``` PostgreSQL has no timezone-naive form of `to_timestamp`. A user must ask for one: ```sql SELECT to_timestamp(1704110400) AT TIME ZONE 'UTC'; -- 2024-01-01 12:00:00 | timestamp without time zone ``` **DuckDB v1.5.2** ```sql SELECT current_setting('TimeZone'), to_timestamp(1704110400), typeof(to_timestamp(1704110400)); -- America/Chicago | 2024-01-01 06:00:00-06 | TIMESTAMP WITH TIME ZONE SET TimeZone = 'America/Denver'; SELECT to_timestamp(1704110400); -- 2024-01-01 05:00:00-07 SELECT epoch(to_timestamp(1704110400)); -- 1704110400.0 ``` DuckDB defaults its session time zone to the operating system zone, so it is never naive. The naive form is `make_timestamp(<microseconds>)`, which returns `TIMESTAMP`. **What the comparison supports, and what it does not** - The type and the render rule of this PR agree with both engines. A one-argument epoch conversion is timezone-aware and follows the session time zone. - The default differs, and this PR keeps DataFusion's default. DataFusion leaves the session time zone unset, so `from_unixtime` stays naive out of the box. PostgreSQL and DuckDB always have a session zone. - The fixed-offset string form is not a meaningful comparison against PostgreSQL. `SET TimeZone = '+08:00'` in PostgreSQL means UTC-08:00, because PostgreSQL reads the string POSIX-style. DataFusion reads `'+08:00'` as UTC+08:00. That divergence is separate and is tracked in https://github.com/apache/datafusion/issues/25170. - The out-of-range comparison is not meaningful either. The three engines have three different timestamp ranges. PostgreSQL raises `ERROR: timestamp out of range` at `to_timestamp(-8334601211039)`. DuckDB renders the same value as `262145-12-31 (BC) 23:59:59.000448-04:56`. DataFusion sits between the two, and this PR makes it raise an error rather than panic. ## What changes are included in this PR? ### The fix - `FromUnixtimeFunc` carries an `Option<Arc<str>> timezone` taken from `config.execution.time_zone`, and implements `ScalarUDFImpl::with_updated_config`. `NowFunc` and the `to_timestamp*` functions already work this way. The single-argument form reports and produces `Timestamp(Second, <session tz>)` from both `return_field_from_args` and `invoke_with_args`. - `from_unixtime` moves to `make_udf_function_with_config!`, and its `expr_fn` gains the `@config` marker. The session `ConfigOptions` now reach the function at registration time, and again on every `SET` or `RESET`. - `FromUnixtimeFunc::new()` is deprecated since `56.0.0` in favour of `FromUnixtimeFunc::new_with_config()`. This mirrors the `to_timestamp*` constructors. `Default` still yields the previous timezone-naive behaviour. - The `from_unixtime` documentation description was wrong before this PR. It claimed an RFC3339 string with nanosecond precision and a `Z` suffix, but the function returns an Arrow `Timestamp(Second, ...)`, and the example directly below it shows a `-04:00` offset. The description now states what the function returns, and `docs/source/user-guide/sql/scalar_functions.md` is regenerated with `dev/update_function_docs.sh`. ### The guard Arrow renders a `Timestamp(Second, Some(tz))` through `chrono`'s `DateTime::naive_local`, which panics when the shifted value leaves `NaiveDateTime`'s range. The panic fires at render time, far from `from_unixtime`. The two-argument form can always reach it. Once the one-argument form applies a session zone, it can reach it too. So `invoke_with_args` now checks the bound and returns a `DataFusionError`: ``` Execution error: Cannot convert -8334601211039 to a timestamp in timezone "America/New_York" for function from_unixtime: the local date and time is outside the supported range ``` The check has three parts: 1. A timezone-naive result is never shifted, so the check returns at once. The full `NaiveDateTime` range stays usable, exactly as on `main`. 2. A fast path reads only the array's `min` and `max`. Inside `[NaiveDateTime::MIN + 86400, NaiveDateTime::MAX - 86400]` no zone offset can push a value out of range, so no per-value work happens. 3. Outside that window the check resolves the zone offset per value. `86400` is a deliberate over-bound. The largest offset in the whole IANA database is `Asia/Manila` at `-15:56:08` local mean time, and the largest positive one is `America/Metlakatla` at `+15:13:42`. The largest fixed offset Arrow's `Tz` parser accepts is `+23:59`, which is `86340` seconds. `+24:00` is rejected by the parser. ## What is the testing strategy for this PR? **New sqllogictest file** `datafusion/sqllogictest/test_files/from_unixtime_timezone.slt`, modelled on `to_timestamp_timezone.slt`. It asserts both `arrow_typeof` and the value for: - the session time zone unset, - a fixed offset (`+08:00`), - a named IANA zone (`America/Denver`), - the explicit two-argument form under a set session time zone, which must ignore it, - array (not constant folded) input, - `NULL` input, - equality of one instant across two time zones, - `RESET`, which must restore the timezone-naive behaviour. The `to_unixtime` / `from_unixtime` round trip from the issue is in the file. So are the out-of-range bounds, in both directions, for a named zone and a fixed offset zone, for the one-argument and the two-argument forms, and for array input. **New unit tests** in `datafusion/functions/src/datetime/from_unixtime.rs` cover `return_field_from_args` and `invoke_with_args` with a session time zone set, an explicit time zone that overrides it, and the out-of-range bound. Every bounds test renders its result. The unit tests go through `arrow::util::display::ArrayFormatter`, and the sqllogictest cases use `query P`. This is deliberate: the panic they guard against fires when the value is formatted, not when it is produced, so a test that only computes the value would pass either way. **Verified locally** - `cargo test -p datafusion-sqllogictest --test sqllogictests` — 506 files pass - `cargo test -p datafusion-functions --lib datetime::from_unixtime` — 10 tests pass - `cargo clippy -p datafusion-functions -p datafusion-sql --all-targets -- -D warnings` — clean - `cargo fmt --all --check` and `./ci/scripts/doc_prettier_check.sh` — clean **Verified against a `main` build.** I built `main` at the merge base and this branch as two `datafusion-cli` binaries and diffed their output. With the session time zone unset, eleven probes are byte-identical, including the error paths. The two-argument form is byte-identical across 140 pairs of value and zone. The full detail is in a separate comment on this PR. **Cost of the guard.** `SELECT count(from_unixtime(v)) FROM generate_series(1, 100000000) t(v)`, three runs each, `ci` profile: 3.95 s / 3.65 s / 3.07 s with the zone unset, and 3.09 s / 3.78 s / 3.33 s with `America/Denver`. The two extra kernel passes sit inside the noise. ## Are there any user-facing changes? Yes, four. **1. The behaviour change, which is the bug fix.** When `datafusion.execution.time_zone` is set, `from_unixtime(expr)` returns `Timestamp(Second, <session tz>)` instead of `Timestamp(Second, None)`. The epoch value is unchanged. Only the declared type and the rendered offset differ. With the default unset session time zone nothing changes. **2. An API deprecation.** `FromUnixtimeFunc::new()` is deprecated since `56.0.0` in favour of `FromUnixtimeFunc::new_with_config()`. `Default::default()` stays available and behaves like the old `new()`. **3. A public factory signature change.** `datafusion::functions::datetime::from_unixtime()` becomes `from_unixtime(&ConfigOptions)`, because it now uses `make_udf_function_with_config!`. This breaks callers of that factory. It is the established convention for config-aware functions in this module: `now` and all five `to_timestamp*` functions already have this signature on `main`, with no zero-argument shim. The `expr_fn` wrapper `datafusion::functions::expr_fn::from_unixtime(expr)` keeps its signature. **4. A new error in place of a panic.** See below. ### Out of range values Arrow renders a `Timestamp(Second, Some(tz))` value by a shift of the UTC instant by the zone's offset. It does that with `chrono`'s `DateTime::naive_local`, which **panics** when the shifted value leaves `NaiveDateTime`'s range. The panic fires when the value is formatted, not when it is cast, so it surfaces far from where it starts: ```sql SELECT from_unixtime(-8334601211039, 'America/New_York'); -- thread 'main' panicked at chrono-0.4.45/src/datetime/mod.rs:579:14: -- Local time out of range for `NaiveDateTime` ``` This PR replaces that panic with an error. The check applies to both forms. Its deliberate limits: - **Timezone-naive results are unaffected.** They are never shifted, so the full `NaiveDateTime` range stays usable, exactly as on `main`. - **A value that is out of range in UTC as well is left to the cast**, which already reports it (`Cast error: Failed to convert 8210266876800 to datetime for ...`). No error message changes. - **A query that consumed an out-of-range value without a render now returns an error.** On `main` such a value is only fatal at render time, so `SELECT to_unixtime(from_unixtime(-8334601211039, 'America/New_York'))` returns `-8334601211039` today and errors after this PR. Every query that produced a rendered result keeps its behaviour. ### Known interaction with issue 16594 This removes the panic reproducer in https://github.com/apache/datafusion/issues/16594, which I verified directly. `from_unixtime(-8334601211038 - 1, 'America/New_York')` errors instead of a panic, and `from_unixtime(8210266876799 + 1, 'America/New_York')` keeps its existing cast error. It does **not** close that issue. The issue asks that *all* `Int64` values convert, and that is not reachable while `chrono::NaiveDateTime` renders the value. The underlying defect is an unguarded `naive_local()` in the Arrow timestamp-with-timezone formatter. It stays reachable with no `from_unixtime` at all: ```sql SELECT arrow_cast(-8334601211039, 'Timestamp(Second, Some("America/New_York"))'); -- same panic SET datafusion.execution.time_zone = 'America/New_York'; SELECT to_timestamp_seconds(-8334601211039); -- same panic, on main and on this branch ``` A proper guard belongs in Arrow. The check here only stops `from_unixtime` from handing the formatter a value the formatter cannot render. ### Merge order with PR 25175 https://github.com/apache/datafusion/pull/25175 pins today's `from_unixtime` behaviour in a characterization test file. **No assertion in it breaks.** Its SECTION 10 cases run with the session time zone unset, and this PR keeps `Timestamp(Second, None)` in that case. I replayed both assertions on this branch and they still pass: ``` Timestamp(s) 2024-07-01T00:00:00 Timestamp(s, "America/Denver") 2024-06-30T18:00:00-06:00 ``` The comment above them says `from_unixtime` "ignores `datafusion.execution.time_zone`". That comment goes stale when this PR lands, so whichever merges second must reword it. 🤖 Generated with [Claude Code](https://claude.com/claude-code) --------- Co-authored-by: Claude Opus 5 <[email protected]> --- datafusion/functions/src/datetime/from_unixtime.rs | 473 ++++++++++++++++++++- datafusion/functions/src/datetime/mod.rs | 8 +- datafusion/sql/src/unparser/expr.rs | 2 +- .../test_files/from_unixtime_timezone.slt | 168 ++++++++ docs/source/user-guide/sql/scalar_functions.md | 19 +- 5 files changed, 646 insertions(+), 24 deletions(-) diff --git a/datafusion/functions/src/datetime/from_unixtime.rs b/datafusion/functions/src/datetime/from_unixtime.rs index 85494f3abf..8af4553f5c 100644 --- a/datafusion/functions/src/datetime/from_unixtime.rs +++ b/datafusion/functions/src/datetime/from_unixtime.rs @@ -17,21 +17,34 @@ use std::sync::Arc; +use arrow::array::AsArray; +use arrow::array::timezone::Tz; +use arrow::compute::kernels::aggregate::{max, min}; use arrow::datatypes::DataType::{Int64, Timestamp, Utf8}; +use arrow::datatypes::Int64Type; use arrow::datatypes::TimeUnit::Second; use arrow::datatypes::{DataType, Field, FieldRef}; +use chrono::{DateTime, NaiveDateTime, Offset, TimeDelta, TimeZone}; +use datafusion_common::config::ConfigOptions; use datafusion_common::{Result, ScalarValue, exec_err, internal_err}; use datafusion_expr::TypeSignature::Exact; use datafusion_expr::sort_properties::{ExprProperties, SortProperties}; use datafusion_expr::{ - ColumnarValue, Documentation, ReturnFieldArgs, ScalarFunctionArgs, ScalarUDFImpl, - Signature, Volatility, + ColumnarValue, Documentation, ReturnFieldArgs, ScalarFunctionArgs, ScalarUDF, + ScalarUDFImpl, Signature, Volatility, }; use datafusion_macros::user_doc; #[user_doc( doc_section(label = "Time and Date Functions"), - description = "Converts an integer to RFC3339 timestamp format (`YYYY-MM-DDT00:00:00.000000000Z`). Integers and unsigned integers are interpreted as seconds since the unix epoch (`1970-01-01T00:00:00Z`) return the corresponding timestamp.", + description = r#" +Converts an integer to a timestamp with second precision (`Timestamp(Second)`). +The integer is interpreted as the number of seconds since the unix epoch +(`1970-01-01T00:00:00Z`). + +If the optional `timezone` argument is omitted, the timestamp is returned in the +session time zone (`datafusion.execution.time_zone`), which is unset (i.e. +timezone-naive) by default."#, syntax_example = "from_unixtime(expression[, timezone])", sql_example = r#"```sql > select from_unixtime(1599572549, 'America/New_York'); @@ -40,31 +53,59 @@ use datafusion_macros::user_doc; +-----------------------------------------------------------+ | 2020-09-08T09:42:29-04:00 | +-----------------------------------------------------------+ + +-- Without an explicit timezone the session time zone is used +> SET datafusion.execution.time_zone = 'America/New_York'; +> select from_unixtime(1599572549); ++----------------------------------+ +| from_unixtime(Int64(1599572549)) | ++----------------------------------+ +| 2020-09-08T09:42:29-04:00 | ++----------------------------------+ ```"#, standard_argument(name = "expression",), argument( name = "timezone", - description = "Optional timezone to use when converting the integer to a timestamp. If not provided, the default timezone is UTC." + description = "Optional timezone to use when converting the integer to a timestamp. If not provided, the session time zone (`datafusion.execution.time_zone`) is used, which is unset (timezone-naive) by default." ) )] #[derive(Debug, PartialEq, Eq, Hash)] pub struct FromUnixtimeFunc { signature: Signature, + /// Timezone from `datafusion.execution.time_zone`, used by the + /// single-argument form. The two-argument form always uses the timezone + /// given explicitly as the second argument. + timezone: Option<Arc<str>>, } impl Default for FromUnixtimeFunc { fn default() -> Self { - Self::new() + Self::new_with_config(&ConfigOptions::default()) } } impl FromUnixtimeFunc { + #[deprecated(since = "56.0.0", note = "use `new_with_config` instead")] + /// Deprecated constructor retained for backwards compatibility. + /// + /// Prefer [`FromUnixtimeFunc::new_with_config`], which picks up the session + /// time zone from [`ConfigOptions`]. This helper mirrors the canonical + /// default (no timezone) provided by `ConfigOptions::default()`. pub fn new() -> Self { + Self::new_with_config(&ConfigOptions::default()) + } + + pub fn new_with_config(config: &ConfigOptions) -> Self { Self { signature: Signature::one_of( vec![Exact(vec![Int64, Utf8]), Exact(vec![Int64])], Volatility::Immutable, ), + timezone: config + .execution + .time_zone + .as_ref() + .map(|tz| Arc::from(tz.as_str())), } } } @@ -78,12 +119,19 @@ impl ScalarUDFImpl for FromUnixtimeFunc { &self.signature } + fn with_updated_config(&self, config: &ConfigOptions) -> Option<ScalarUDF> { + Some(Self::new_with_config(config).into()) + } + fn return_field_from_args(&self, args: ReturnFieldArgs) -> Result<FieldRef> { // Length check handled in the signature debug_assert!(matches!(args.scalar_arguments.len(), 1 | 2)); if args.scalar_arguments.len() == 1 { - Ok(Field::new(self.name(), Timestamp(Second, None), true).into()) + Ok( + Field::new(self.name(), Timestamp(Second, self.timezone.clone()), true) + .into(), + ) } else { args.scalar_arguments[1] .and_then(|sv| { @@ -132,20 +180,25 @@ impl ScalarUDFImpl for FromUnixtimeFunc { ); } - match len { - 1 => args[0].cast_to(&Timestamp(Second, None), None), + let timezone = match len { + 1 => self.timezone.clone(), 2 => match &args[1] { - ColumnarValue::Scalar(ScalarValue::Utf8(Some(tz))) => args[0] - .cast_to(&Timestamp(Second, Some(Arc::from(tz.to_string()))), None), + ColumnarValue::Scalar(ScalarValue::Utf8(Some(tz))) => { + Some(Arc::<str>::from(tz.as_str())) + } _ => { - exec_err!( + return exec_err!( "Unsupported data type {} for function from_unixtime", args[1].data_type() - ) + ); } }, _ => unreachable!(), - } + }; + + validate_local_datetime_range(&args[0], timezone.as_deref())?; + + args[0].cast_to(&Timestamp(Second, timezone), None) } fn output_ordering(&self, inputs: &[ExprProperties]) -> Result<SortProperties> { @@ -171,17 +224,317 @@ impl ScalarUDFImpl for FromUnixtimeFunc { } } +/// An upper bound on the magnitude of any time zone's UTC offset, rounded up to +/// a whole day. Real offsets never exceed 15 hours (including the historical +/// local mean time offsets used before a zone's first transition). +const MAX_TIMEZONE_OFFSET_SECONDS: i64 = 24 * 60 * 60; + +/// The range of `Timestamp(Second)` values whose local date and time is +/// representable in *every* time zone. +/// +/// Values inside this range never need a per-value check: shifting them by any +/// possible UTC offset keeps them inside [`NaiveDateTime`]'s range. +fn always_representable_range() -> (i64, i64) { + ( + NaiveDateTime::MIN.and_utc().timestamp() + MAX_TIMEZONE_OFFSET_SECONDS, + NaiveDateTime::MAX.and_utc().timestamp() - MAX_TIMEZONE_OFFSET_SECONDS, + ) +} + +/// Returns an error if any value in `values` has no representable local date +/// and time in `timezone`. +/// +/// A `Timestamp(Second, Some(tz))` is *rendered* by shifting the UTC instant by +/// the zone's offset. Arrow does that with `DateTime::naive_local`, which +/// **panics** when the shifted value falls outside [`NaiveDateTime`]'s range, +/// so such values have to be rejected before they are produced: the panic would +/// otherwise happen far away from `from_unixtime`, when the value is formatted. +/// +/// See <https://github.com/apache/datafusion/issues/16594>. +fn validate_local_datetime_range( + values: &ColumnarValue, + timezone: Option<&str>, +) -> Result<()> { + // A timezone naive timestamp is never shifted, so it can only be out of + // range if the UTC instant itself is, which the cast already rejects. + let Some(timezone) = timezone else { + return Ok(()); + }; + + let (safe_min, safe_max) = always_representable_range(); + + let (value_min, value_max) = match values { + ColumnarValue::Scalar(ScalarValue::Int64(Some(value))) => (*value, *value), + // NULL (and anything else the cast will reject) needs no check. + ColumnarValue::Scalar(_) => return Ok(()), + ColumnarValue::Array(array) => { + let array = array.as_primitive::<Int64Type>(); + match (min(array), max(array)) { + (Some(value_min), Some(value_max)) => (value_min, value_max), + // All null. + _ => return Ok(()), + } + } + }; + + // Fast path: no value can overflow, whatever the zone's offset is. + if value_min >= safe_min && value_max <= safe_max { + return Ok(()); + } + + let Ok(tz) = timezone.parse::<Tz>() else { + // An unparsable time zone is reported by the cast below. + return Ok(()); + }; + + let check = |seconds: i64| -> Result<()> { + if seconds >= safe_min && seconds <= safe_max { + return Ok(()); + } + let Some(utc) = DateTime::from_timestamp(seconds, 0) else { + // Not representable as an instant at all: the cast reports this. + return Ok(()); + }; + let utc = utc.naive_utc(); + let offset = tz.offset_from_utc_datetime(&utc).fix().local_minus_utc(); + if utc + .checked_add_signed(TimeDelta::seconds(i64::from(offset))) + .is_none() + { + return exec_err!( + "Cannot convert {seconds} to a timestamp in timezone \"{timezone}\" \ + for function from_unixtime: the local date and time is outside the \ + supported range" + ); + } + Ok(()) + }; + + match values { + ColumnarValue::Scalar(_) => check(value_min), + ColumnarValue::Array(array) => { + // Only the values near the limits do any real work; `check` returns + // immediately for everything inside the always representable range. + for value in array.as_primitive::<Int64Type>().iter().flatten() { + check(value)?; + } + Ok(()) + } + } +} + #[cfg(test)] mod test { use crate::datetime::from_unixtime::FromUnixtimeFunc; + use arrow::array::{ArrayRef, Int64Array}; use arrow::datatypes::TimeUnit::Second; - use arrow::datatypes::{DataType, Field}; + use arrow::datatypes::{DataType, Field, FieldRef}; + use arrow::util::display::{ArrayFormatter, FormatOptions}; + use datafusion_common::Result; use datafusion_common::ScalarValue; use datafusion_common::ScalarValue::Int64; use datafusion_common::config::ConfigOptions; - use datafusion_expr::{ColumnarValue, ScalarFunctionArgs, ScalarUDFImpl}; + use datafusion_expr::{ + ColumnarValue, ReturnFieldArgs, ScalarFunctionArgs, ScalarUDFImpl, + }; use std::sync::Arc; + /// The last second since the epoch that `chrono::NaiveDateTime` can + /// represent (`+262142-12-31T23:59:59`). + const MAX_UTC_SECONDS: i64 = 8210266876799; + /// The first second since the epoch that `chrono::NaiveDateTime` can + /// represent (`-262143-01-01T00:00:00`). + const MIN_UTC_SECONDS: i64 = -8334601228800; + + /// Invoke the single argument form with `datafusion.execution.time_zone` + /// set to `timezone`. + fn from_unixtime_session_tz( + seconds: i64, + timezone: Option<&str>, + ) -> Result<ColumnarValue> { + let mut options = ConfigOptions::default(); + options.execution.time_zone = timezone.map(str::to_string); + let func = FromUnixtimeFunc::new_with_config(&options); + + let arg_field: FieldRef = Field::new("a", DataType::Int64, true).into(); + let return_field = Field::new( + "f", + DataType::Timestamp(Second, timezone.map(Arc::from)), + true, + ) + .into(); + func.invoke_with_args(ScalarFunctionArgs { + args: vec![ColumnarValue::Scalar(Int64(Some(seconds)))], + arg_fields: vec![arg_field], + number_rows: 1, + return_field, + config_options: Arc::new(options), + }) + } + + /// Invoke the two argument form with an explicit `timezone`. + fn from_unixtime_explicit_tz( + values: ColumnarValue, + timezone: &str, + ) -> Result<ColumnarValue> { + let number_rows = match &values { + ColumnarValue::Array(array) => array.len(), + ColumnarValue::Scalar(_) => 1, + }; + let arg_fields: Vec<FieldRef> = vec![ + Field::new("a", DataType::Int64, true).into(), + Field::new("b", DataType::Utf8, true).into(), + ]; + let return_field = Field::new( + "f", + DataType::Timestamp(Second, Some(Arc::from(timezone))), + true, + ) + .into(); + FromUnixtimeFunc::default().invoke_with_args(ScalarFunctionArgs { + args: vec![ + values, + ColumnarValue::Scalar(ScalarValue::Utf8(Some(timezone.to_string()))), + ], + arg_fields, + number_rows, + return_field, + config_options: Arc::new(ConfigOptions::default()), + }) + } + + /// Render the result the way a client would. + /// + /// The out of range local date and time panics when the value is + /// *formatted*, not when it is produced, so every bounds test has to go + /// through a formatter to be meaningful. + fn display(value: ColumnarValue) -> String { + let array = value.to_array(1).unwrap(); + let options = FormatOptions::default(); + let formatter = ArrayFormatter::try_new(array.as_ref(), &options).unwrap(); + formatter.value(0).to_string() + } + + /// The single argument form used to silently produce values that panicked + /// downstream once a session time zone was set. + /// + /// `America/New_York` is at -04:56:02 (local mean time) that far in the + /// past, so the local date and time runs out one offset earlier than the + /// UTC instant does. + #[test] + fn test_named_timezone_lower_bound() { + let last_ok = MIN_UTC_SECONDS + 17762; + assert_eq!(last_ok, -8334601211038); + + let value = from_unixtime_session_tz(last_ok, Some("America/New_York")).unwrap(); + assert_eq!(display(value), "-262143-01-01T00:00:00-04:56"); + + let err = from_unixtime_session_tz(last_ok - 1, Some("America/New_York")) + .expect_err("expected an out of range error, not a value that panics"); + assert!( + err.message().contains("outside the supported range"), + "unexpected error: {err}" + ); + } + + /// The same bound in the other direction: a zone that is *ahead* of UTC + /// runs out of local date and time before the UTC instant does. + /// + /// This is the two argument form, which panicked on `main` as well + /// (<https://github.com/apache/datafusion/issues/16594>). + #[test] + fn test_named_timezone_upper_bound() { + // +09:00, with no daylight saving. + let last_ok = MAX_UTC_SECONDS - 32400; + + let value = from_unixtime_explicit_tz( + ColumnarValue::Scalar(Int64(Some(last_ok))), + "Asia/Tokyo", + ) + .unwrap(); + assert_eq!(display(value), "+262142-12-31T23:59:59+09:00"); + + let err = from_unixtime_explicit_tz( + ColumnarValue::Scalar(Int64(Some(last_ok + 1))), + "Asia/Tokyo", + ) + .expect_err("expected an out of range error, not a value that panics"); + assert!( + err.message().contains("outside the supported range"), + "unexpected error: {err}" + ); + } + + /// Fixed offset zones hit exactly the same bound. + #[test] + fn test_fixed_offset_upper_bound() { + let last_ok = MAX_UTC_SECONDS - 28800; + + let value = from_unixtime_session_tz(last_ok, Some("+08:00")).unwrap(); + assert_eq!(display(value), "+262142-12-31T23:59:59+08:00"); + + let err = from_unixtime_session_tz(last_ok + 1, Some("+08:00")) + .expect_err("expected an out of range error, not a value that panics"); + assert!( + err.message().contains("outside the supported range"), + "unexpected error: {err}" + ); + } + + #[test] + fn test_fixed_offset_lower_bound() { + let last_ok = MIN_UTC_SECONDS + 28800; + + let value = from_unixtime_explicit_tz( + ColumnarValue::Scalar(Int64(Some(last_ok))), + "-08:00", + ) + .unwrap(); + assert_eq!(display(value), "-262143-01-01T00:00:00-08:00"); + + let err = from_unixtime_explicit_tz( + ColumnarValue::Scalar(Int64(Some(last_ok - 1))), + "-08:00", + ) + .expect_err("expected an out of range error, not a value that panics"); + assert!( + err.message().contains("outside the supported range"), + "unexpected error: {err}" + ); + } + + /// Array input is checked too, not just the constant folded scalar case. + #[test] + fn test_array_input_is_bounds_checked() { + let array: ArrayRef = + Arc::new(Int64Array::from(vec![Some(0), None, Some(MIN_UTC_SECONDS)])); + let err = + from_unixtime_explicit_tz(ColumnarValue::Array(array), "America/New_York") + .expect_err("expected an out of range error, not a value that panics"); + assert!( + err.message().contains("outside the supported range"), + "unexpected error: {err}" + ); + + // In range values still work. + let array: ArrayRef = Arc::new(Int64Array::from(vec![Some(0), None])); + let value = + from_unixtime_explicit_tz(ColumnarValue::Array(array), "America/New_York") + .unwrap(); + assert_eq!(display(value), "1969-12-31T19:00:00-05:00"); + } + + /// Without a time zone the value is never shifted, so the full + /// `NaiveDateTime` range stays usable, exactly as before this bound check. + #[test] + fn test_timezone_naive_extremes_are_still_accepted() { + let value = from_unixtime_session_tz(MIN_UTC_SECONDS, None).unwrap(); + assert_eq!(display(value), "-262143-01-01T00:00:00"); + + let value = from_unixtime_session_tz(MAX_UTC_SECONDS, None).unwrap(); + assert_eq!(display(value), "+262142-12-31T23:59:59"); + } + #[test] fn test_without_timezone() { let arg_field = Arc::new(Field::new("a", DataType::Int64, true)); @@ -192,7 +545,7 @@ mod test { return_field: Field::new("f", DataType::Timestamp(Second, None), true).into(), config_options: Arc::new(ConfigOptions::default()), }; - let result = FromUnixtimeFunc::new().invoke_with_args(args).unwrap(); + let result = FromUnixtimeFunc::default().invoke_with_args(args).unwrap(); match result { ColumnarValue::Scalar(ScalarValue::TimestampSecond(Some(sec), None)) => { @@ -202,6 +555,92 @@ mod test { } } + /// The single argument form reports (and produces) the session time zone + /// configured via `datafusion.execution.time_zone`. + #[test] + fn test_session_timezone_is_used_without_explicit_timezone() { + let mut options = ConfigOptions::default(); + options.execution.time_zone = Some("America/Denver".to_string()); + + let func = FromUnixtimeFunc::new_with_config(&options); + + let arg_field: FieldRef = Field::new("a", DataType::Int64, true).into(); + let scalar_arguments = vec![None]; + let return_field = func + .return_field_from_args(ReturnFieldArgs { + arg_fields: std::slice::from_ref(&arg_field), + scalar_arguments: &scalar_arguments, + }) + .unwrap(); + assert_eq!( + return_field.data_type(), + &DataType::Timestamp(Second, Some(Arc::from("America/Denver"))) + ); + + let args = ScalarFunctionArgs { + args: vec![ColumnarValue::Scalar(Int64(Some(1729900800)))], + arg_fields: vec![arg_field], + number_rows: 1, + return_field, + config_options: Arc::new(options), + }; + let result = func.invoke_with_args(args).unwrap(); + + match result { + ColumnarValue::Scalar(ScalarValue::TimestampSecond(Some(sec), Some(tz))) => { + assert_eq!(sec, 1729900800); + assert_eq!(tz.as_ref(), "America/Denver"); + } + other => panic!("Expected timezone aware scalar value, got {other:?}"), + } + } + + /// An explicit second argument wins over the session time zone. + #[test] + fn test_explicit_timezone_overrides_session_timezone() { + let mut options = ConfigOptions::default(); + options.execution.time_zone = Some("America/Denver".to_string()); + + let func = FromUnixtimeFunc::new_with_config(&options); + + let arg_fields: Vec<FieldRef> = vec![ + Field::new("a", DataType::Int64, true).into(), + Field::new("b", DataType::Utf8, true).into(), + ]; + let tz_arg = ScalarValue::Utf8(Some("+08:00".to_string())); + let scalar_arguments = vec![None, Some(&tz_arg)]; + let return_field = func + .return_field_from_args(ReturnFieldArgs { + arg_fields: &arg_fields, + scalar_arguments: &scalar_arguments, + }) + .unwrap(); + assert_eq!( + return_field.data_type(), + &DataType::Timestamp(Second, Some(Arc::from("+08:00"))) + ); + + let args = ScalarFunctionArgs { + args: vec![ + ColumnarValue::Scalar(Int64(Some(1729900800))), + ColumnarValue::Scalar(tz_arg.clone()), + ], + arg_fields, + number_rows: 1, + return_field, + config_options: Arc::new(options), + }; + let result = func.invoke_with_args(args).unwrap(); + + match result { + ColumnarValue::Scalar(ScalarValue::TimestampSecond(Some(sec), Some(tz))) => { + assert_eq!(sec, 1729900800); + assert_eq!(tz.as_ref(), "+08:00"); + } + other => panic!("Expected timezone aware scalar value, got {other:?}"), + } + } + #[test] fn test_with_timezone() { let arg_fields = vec![ @@ -225,7 +664,7 @@ mod test { .into(), config_options: Arc::new(ConfigOptions::default()), }; - let result = FromUnixtimeFunc::new().invoke_with_args(args).unwrap(); + let result = FromUnixtimeFunc::default().invoke_with_args(args).unwrap(); match result { ColumnarValue::Scalar(ScalarValue::TimestampSecond(Some(sec), Some(tz))) => { diff --git a/datafusion/functions/src/datetime/mod.rs b/datafusion/functions/src/datetime/mod.rs index 39b9453295..9246d1d25d 100644 --- a/datafusion/functions/src/datetime/mod.rs +++ b/datafusion/functions/src/datetime/mod.rs @@ -47,12 +47,12 @@ 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!(from_unixtime::FromUnixtimeFunc, from_unixtime); 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); make_udf_function!(to_time::ToTimeFunc, to_time); make_udf_function!(to_unixtime::ToUnixtimeFunc, to_unixtime); +make_udf_function_with_config!(from_unixtime::FromUnixtimeFunc, from_unixtime); make_udf_function_with_config!(to_timestamp::ToTimestampFunc, to_timestamp); make_udf_function_with_config!( to_timestamp::ToTimestampSecondsFunc, @@ -79,8 +79,8 @@ pub mod expr_fn { "returns current UTC time as a Time64 value", ),( from_unixtime, - "converts an integer to RFC3339 timestamp format string", - unixtime + "converts an integer of epoch seconds to a second-precision timestamp", + @config unixtime ),( date_bin, "coerces an arbitrary timestamp to the start of the nearest specified interval", @@ -281,7 +281,7 @@ pub fn functions() -> Vec<Arc<ScalarUDF>> { date_bin(), date_part(), date_trunc(), - from_unixtime(), + from_unixtime(&config), make_date(), make_time(), now(&config), diff --git a/datafusion/sql/src/unparser/expr.rs b/datafusion/sql/src/unparser/expr.rs index 108be43d44..92c9101afb 100644 --- a/datafusion/sql/src/unparser/expr.rs +++ b/datafusion/sql/src/unparser/expr.rs @@ -3480,7 +3480,7 @@ mod tests { ] { let unparser = Unparser::new(dialect.as_ref()); let expr = Expr::ScalarFunction(ScalarFunction { - func: Arc::new(ScalarUDF::from(FromUnixtimeFunc::new())), + func: Arc::new(ScalarUDF::from(FromUnixtimeFunc::default())), args: vec![col("date_col")], }); diff --git a/datafusion/sqllogictest/test_files/from_unixtime_timezone.slt b/datafusion/sqllogictest/test_files/from_unixtime_timezone.slt new file mode 100644 index 0000000000..a295fe48ef --- /dev/null +++ b/datafusion/sqllogictest/test_files/from_unixtime_timezone.slt @@ -0,0 +1,168 @@ +# 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. + +########## +## from_unixtime timezone tests +## +## https://github.com/apache/datafusion/issues/12892 +########## + +## Make sure we start from the default (unset) session time zone +statement ok +RESET datafusion.execution.time_zone + +## Test 1: session time zone unset -> timezone naive, as before +query TP +SELECT arrow_typeof(from_unixtime(1704110400)), from_unixtime(1704110400); +---- +Timestamp(s) 2024-01-01T12:00:00 + +## Test 2: fixed offset session time zone +statement ok +SET datafusion.execution.time_zone = '+08:00'; + +query TP +SELECT arrow_typeof(from_unixtime(1704110400)), from_unixtime(1704110400); +---- +Timestamp(s, "+08:00") 2024-01-01T20:00:00+08:00 + +## The reproducer from the issue: to_unixtime/from_unixtime must round trip +query I +SELECT to_unixtime(from_unixtime(1704110400)); +---- +1704110400 + +## Test 3: named IANA session time zone +statement ok +SET datafusion.execution.time_zone = 'America/Denver'; + +query TP +SELECT arrow_typeof(from_unixtime(1704110400)), from_unixtime(1704110400); +---- +Timestamp(s, "America/Denver") 2024-01-01T05:00:00-07:00 + +# January alone (`-07:00`) cannot tell a DST-aware zone from a fixed `-07:00` +# offset. A July instant must render `-06:00`, which only a DST-aware zone does. +query TP +SELECT arrow_typeof(from_unixtime(1719835200)), from_unixtime(1719835200); +---- +Timestamp(s, "America/Denver") 2024-07-01T06:00:00-06:00 + +## Test 4: an explicit timezone argument wins over the session time zone +query TP +SELECT + arrow_typeof(from_unixtime(1704110400, 'America/New_York')), + from_unixtime(1704110400, 'America/New_York'); +---- +Timestamp(s, "America/New_York") 2024-01-01T07:00:00-05:00 + +## Test 5: array (not constant folded) input carries the session time zone too +query TP +SELECT arrow_typeof(from_unixtime(v)), from_unixtime(v) FROM (VALUES (1704110400), (0)) AS t(v); +---- +Timestamp(s, "America/Denver") 2024-01-01T05:00:00-07:00 +Timestamp(s, "America/Denver") 1969-12-31T17:00:00-07:00 + +## Test 6: NULL input stays NULL, but keeps the session time zone in its type +query TP +SELECT arrow_typeof(from_unixtime(NULL)), from_unixtime(NULL); +---- +Timestamp(s, "America/Denver") NULL + +query TP +SELECT arrow_typeof(from_unixtime(arrow_cast(NULL, 'Int64'), 'America/New_York')), from_unixtime(arrow_cast(NULL, 'Int64'), 'America/New_York'); +---- +Timestamp(s, "America/New_York") NULL + +## Test 7: the underlying instant is unchanged - only the displayed time zone differs +query B +SELECT from_unixtime(1704110400) = from_unixtime(1704110400, 'America/New_York'); +---- +true + +## Test 8: RESET restores the timezone naive behaviour +statement ok +RESET datafusion.execution.time_zone + +query TP +SELECT arrow_typeof(from_unixtime(1704110400)), from_unixtime(1704110400); +---- +Timestamp(s) 2024-01-01T12:00:00 + +########## +## Out of range values +## +## A `Timestamp(Second, Some(tz))` is rendered by shifting the UTC instant by +## the zone's offset, and that shifted value has to stay inside the range +## chrono's `NaiveDateTime` can represent, otherwise formatting the value +## panics. +## +## https://github.com/apache/datafusion/issues/16594 +########## + +## The last value `America/New_York` can represent (its offset is -04:56 that +## far in the past, so the local time runs out before the UTC instant does) +query P +SELECT from_unixtime(-8334601211038, 'America/New_York'); +---- +-262143-01-01T00:00:00-04:56 + +## One second earlier is an error, not a panic +query error DataFusion error: Execution error: Cannot convert \-8334601211039 to a timestamp in timezone "America/New_York" for function from_unixtime: the local date and time is outside the supported range +SELECT from_unixtime(-8334601211039, 'America/New_York'); + +## The same bound applies to the session time zone form +statement ok +SET datafusion.execution.time_zone = 'America/New_York'; + +query P +SELECT from_unixtime(-8334601211038); +---- +-262143-01-01T00:00:00-04:56 + +query error DataFusion error: Execution error: Cannot convert \-8334601211039 to a timestamp in timezone "America/New_York" for function from_unixtime: the local date and time is outside the supported range +SELECT from_unixtime(-8334601211039); + +## ... and to array (not constant folded) input +query error DataFusion error: Execution error: Cannot convert \-8334601211039 to a timestamp in timezone "America/New_York" for function from_unixtime: the local date and time is outside the supported range +SELECT from_unixtime(v) FROM (VALUES (0), (-8334601211039)) AS t(v); + +## A zone that is ahead of UTC runs out at the other end +statement ok +SET datafusion.execution.time_zone = '+08:00'; + +query P +SELECT from_unixtime(8210266847999); +---- ++262142-12-31T23:59:59+08:00 + +query error DataFusion error: Execution error: Cannot convert 8210266848000 to a timestamp in timezone "\+08:00" for function from_unixtime: the local date and time is outside the supported range +SELECT from_unixtime(8210266848000); + +statement ok +RESET datafusion.execution.time_zone + +## Without a time zone the full range stays usable +query P +SELECT from_unixtime(-8334601228800); +---- +-262143-01-01T00:00:00 + +query P +SELECT from_unixtime(8210266876799); +---- ++262142-12-31T23:59:59 diff --git a/docs/source/user-guide/sql/scalar_functions.md b/docs/source/user-guide/sql/scalar_functions.md index 6726b7e145..11f50b84e3 100644 --- a/docs/source/user-guide/sql/scalar_functions.md +++ b/docs/source/user-guide/sql/scalar_functions.md @@ -2659,7 +2659,13 @@ _Alias of [date_trunc](#date_trunc)._ ### `from_unixtime` -Converts an integer to RFC3339 timestamp format (`YYYY-MM-DDT00:00:00.000000000Z`). Integers and unsigned integers are interpreted as seconds since the unix epoch (`1970-01-01T00:00:00Z`) return the corresponding timestamp. +Converts an integer to a timestamp with second precision (`Timestamp(Second)`). +The integer is interpreted as the number of seconds since the unix epoch +(`1970-01-01T00:00:00Z`). + +If the optional `timezone` argument is omitted, the timestamp is returned in the +session time zone (`datafusion.execution.time_zone`), which is unset (i.e. +timezone-naive) by default. ```sql from_unixtime(expression[, timezone]) @@ -2668,7 +2674,7 @@ from_unixtime(expression[, timezone]) #### Arguments - **expression**: The expression to operate on. Can be a constant, column, or function, and any combination of operators. -- **timezone**: Optional timezone to use when converting the integer to a timestamp. If not provided, the default timezone is UTC. +- **timezone**: Optional timezone to use when converting the integer to a timestamp. If not provided, the session time zone (`datafusion.execution.time_zone`) is used, which is unset (timezone-naive) by default. #### Example @@ -2679,6 +2685,15 @@ from_unixtime(expression[, timezone]) +-----------------------------------------------------------+ | 2020-09-08T09:42:29-04:00 | +-----------------------------------------------------------+ + +-- Without an explicit timezone the session time zone is used +> SET datafusion.execution.time_zone = 'America/New_York'; +> select from_unixtime(1599572549); ++----------------------------------+ +| from_unixtime(Int64(1599572549)) | ++----------------------------------+ +| 2020-09-08T09:42:29-04:00 | ++----------------------------------+ ``` ### `make_date` --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
