kosiew commented on code in PR #25175: URL: https://github.com/apache/datafusion/pull/25175#discussion_r4082258622
########## datafusion/sqllogictest/test_files/datetime/timestamps_timezone.slt: ########## @@ -0,0 +1,1589 @@ +# 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. + +########## +## Timezone characterization suite +## +## This file is a *characterization* suite: it records what DataFusion actually +## does today for timestamps with time zones, so that any change to those +## semantics shows up as a diff here instead of silently shipping. +## +## Some of what is recorded below is known to be wrong, or at least known to +## disagree with PostgreSQL. Those cases carry a comment saying so and, where +## one exists, a link to the issue. Nothing here is "fixed" by this file -- +## when a fix lands, the expected output changes and the comment goes away. +## +## Related issues: +## https://github.com/apache/datafusion/issues/10368 +## https://github.com/apache/datafusion/issues/10602 +## https://github.com/apache/datafusion/issues/12218 +## https://github.com/apache/datafusion/issues/12892 +## https://github.com/apache/datafusion/issues/13212 +## https://github.com/apache/datafusion/issues/25084 +## https://github.com/apache/datafusion/issues/25095 +## https://github.com/apache/datafusion/issues/25166 +## https://github.com/apache/datafusion/issues/25167 +## https://github.com/apache/datafusion/issues/25170 +## +## Cross-engine notes below quote PostgreSQL with the session `TimeZone` named +## in the comment. The subset the two engines agree on is machine checked +## against a real PostgreSQL in CI by +## test_files/pg_compat/pg_compat_timestamptz.slt, added in +## https://github.com/apache/datafusion/pull/25164. +########## + +########## +## SECTION 0: session time zone unset +## +## NOTE: with `datafusion.execution.time_zone` unset, `::timestamptz` and +## `TIMESTAMP WITH TIME ZONE` produce a time zone *naive* `Timestamp(ns)`. +## The offset in the input string is still honoured (the value is normalized +## to UTC) but the resulting type carries no zone at all, so the "with time +## zone" in the syntax is not reflected in the type. PostgreSQL always +## produces `timestamp with time zone` here. +## +## See https://github.com/apache/datafusion/issues/25166 +########## + +statement ok +RESET datafusion.execution.time_zone + +query T +SELECT arrow_typeof('2024-07-01 12:00:00'::timestamp) +---- +Timestamp(ns) + +query TP +SELECT arrow_typeof('2024-07-01 12:00:00'::timestamptz), '2024-07-01 12:00:00'::timestamptz +---- +Timestamp(ns) 2024-07-01T12:00:00 + +query TP +SELECT arrow_typeof(TIMESTAMP '2024-07-01 12:00:00'), TIMESTAMP '2024-07-01 12:00:00' +---- +Timestamp(ns) 2024-07-01T12:00:00 + +query TP +SELECT arrow_typeof(TIMESTAMP WITH TIME ZONE '2024-07-01 12:00:00'), TIMESTAMP WITH TIME ZONE '2024-07-01 12:00:00' +---- +Timestamp(ns) 2024-07-01T12:00:00 + +query TP +SELECT arrow_typeof('2024-07-01 12:00:00Z'::timestamptz), '2024-07-01 12:00:00Z'::timestamptz +---- +Timestamp(ns) 2024-07-01T12:00:00 + +# The offset is applied (12:00+05:30 -> 06:30 UTC) even though the result type +# is naive, so the value is a UTC instant wearing no label. +query TP +SELECT arrow_typeof('2024-07-01 12:00:00+05:30'::timestamptz), '2024-07-01 12:00:00+05:30'::timestamptz +---- +Timestamp(ns) 2024-07-01T06:30:00 + +statement ok +CREATE TABLE c_unset AS VALUES ('2024-07-01 12:00:00'::timestamptz), ('2024-01-15 12:00:00'::timestamptz) + +query T +SELECT arrow_typeof(column1) FROM c_unset LIMIT 1 +---- +Timestamp(ns) + +query P rowsort +SELECT column1 FROM c_unset +---- +2024-01-15T12:00:00 +2024-07-01T12:00:00 + +statement ok +DROP TABLE c_unset + +########## +## SECTION 1: session time zone +00:00 +########## + +statement ok +SET datafusion.execution.time_zone = '+00:00' + +query T +SELECT arrow_typeof('2024-07-01 12:00:00'::timestamp) +---- +Timestamp(ns) + +query TP +SELECT arrow_typeof('2024-07-01 12:00:00'::timestamptz), '2024-07-01 12:00:00'::timestamptz +---- +Timestamp(ns, "+00:00") 2024-07-01T12:00:00Z + +# `TIMESTAMP '...'` is unaffected by the session time zone: it is always naive. +query TP +SELECT arrow_typeof(TIMESTAMP '2024-07-01 12:00:00'), TIMESTAMP '2024-07-01 12:00:00' +---- +Timestamp(ns) 2024-07-01T12:00:00 + +query TP +SELECT arrow_typeof(TIMESTAMP WITH TIME ZONE '2024-07-01 12:00:00'), TIMESTAMP WITH TIME ZONE '2024-07-01 12:00:00' +---- +Timestamp(ns, "+00:00") 2024-07-01T12:00:00Z + +query TP +SELECT arrow_typeof('2024-07-01 12:00:00+05:30'::timestamptz), '2024-07-01 12:00:00+05:30'::timestamptz +---- +Timestamp(ns, "+00:00") 2024-07-01T06:30:00Z + +statement ok +CREATE TABLE c_utc AS VALUES ('2024-07-01 12:00:00'::timestamptz), ('2024-01-15 12:00:00'::timestamptz) + +query T +SELECT arrow_typeof(column1) FROM c_utc LIMIT 1 +---- +Timestamp(ns, "+00:00") + +query P rowsort +SELECT column1 FROM c_utc +---- +2024-01-15T12:00:00Z +2024-07-01T12:00:00Z + +########## +## SECTION 2: session time zone +05:30 (fixed, non whole hour offset) +########## + +statement ok +SET datafusion.execution.time_zone = '+05:30' + +query TP +SELECT arrow_typeof('2024-07-01 12:00:00'::timestamptz), '2024-07-01 12:00:00'::timestamptz +---- +Timestamp(ns, "+05:30") 2024-07-01T12:00:00+05:30 + +query TP +SELECT arrow_typeof(TIMESTAMP WITH TIME ZONE '2024-07-01 12:00:00'), TIMESTAMP WITH TIME ZONE '2024-07-01 12:00:00' +---- +Timestamp(ns, "+05:30") 2024-07-01T12:00:00+05:30 + +query TP +SELECT arrow_typeof(TIMESTAMP '2024-07-01 12:00:00'), TIMESTAMP '2024-07-01 12:00:00' +---- +Timestamp(ns) 2024-07-01T12:00:00 + +query TP +SELECT arrow_typeof('2024-07-01 12:00:00Z'::timestamptz), '2024-07-01 12:00:00Z'::timestamptz +---- +Timestamp(ns, "+05:30") 2024-07-01T17:30:00+05:30 + +statement ok +CREATE TABLE c_0530 AS VALUES ('2024-07-01 12:00:00'::timestamptz), ('2024-01-15 12:00:00'::timestamptz) + +query T +SELECT arrow_typeof(column1) FROM c_0530 LIMIT 1 +---- +Timestamp(ns, "+05:30") + +query P rowsort +SELECT column1 FROM c_0530 +---- +2024-01-15T12:00:00+05:30 +2024-07-01T12:00:00+05:30 + +########## +## SECTION 3: session time zone America/Denver (named zone, observes DST) +########## + +statement ok +SET datafusion.execution.time_zone = 'America/Denver' + +query TP +SELECT arrow_typeof('2024-07-01 12:00:00'::timestamptz), '2024-07-01 12:00:00'::timestamptz +---- +Timestamp(ns, "America/Denver") 2024-07-01T12:00:00-06:00 + +query TP +SELECT arrow_typeof(TIMESTAMP WITH TIME ZONE '2024-07-01 12:00:00'), TIMESTAMP WITH TIME ZONE '2024-07-01 12:00:00' +---- +Timestamp(ns, "America/Denver") 2024-07-01T12:00:00-06:00 + +query TP +SELECT arrow_typeof(TIMESTAMP '2024-07-01 12:00:00'), TIMESTAMP '2024-07-01 12:00:00' +---- +Timestamp(ns) 2024-07-01T12:00:00 + +# Winter (MST, -07:00) vs summer (MDT, -06:00): the same wall clock string maps +# to a different UTC instant depending on the date. Matches PostgreSQL. +query TP +SELECT arrow_typeof('2024-01-15 12:00:00'::timestamptz), '2024-01-15 12:00:00'::timestamptz +---- +Timestamp(ns, "America/Denver") 2024-01-15T12:00:00-07:00 + +statement ok +CREATE TABLE c_denver AS VALUES ('2024-07-01 12:00:00'::timestamptz), ('2024-01-15 12:00:00'::timestamptz) + +query T +SELECT arrow_typeof(column1) FROM c_denver LIMIT 1 +---- +Timestamp(ns, "America/Denver") + +query P rowsort +SELECT column1 FROM c_denver +---- +2024-01-15T12:00:00-07:00 +2024-07-01T12:00:00-06:00 + +########## +## SECTION 4: session time zone Europe/Brussels +########## + +statement ok +SET datafusion.execution.time_zone = 'Europe/Brussels' + +query TP +SELECT arrow_typeof('2024-07-01 12:00:00'::timestamptz), '2024-07-01 12:00:00'::timestamptz +---- +Timestamp(ns, "Europe/Brussels") 2024-07-01T12:00:00+02:00 + +query TP +SELECT arrow_typeof(TIMESTAMP WITH TIME ZONE '2024-07-01 12:00:00'), TIMESTAMP WITH TIME ZONE '2024-07-01 12:00:00' +---- +Timestamp(ns, "Europe/Brussels") 2024-07-01T12:00:00+02:00 + +query TP +SELECT arrow_typeof('2024-01-15 12:00:00'::timestamptz), '2024-01-15 12:00:00'::timestamptz +---- +Timestamp(ns, "Europe/Brussels") 2024-01-15T12:00:00+01:00 + +statement ok +CREATE TABLE c_brussels AS VALUES ('2024-07-01 12:00:00'::timestamptz), ('2024-01-15 12:00:00'::timestamptz) + +query T +SELECT arrow_typeof(column1) FROM c_brussels LIMIT 1 +---- +Timestamp(ns, "Europe/Brussels") + +query P rowsort +SELECT column1 FROM c_brussels +---- +2024-01-15T12:00:00+01:00 +2024-07-01T12:00:00+02:00 + +statement ok +RESET datafusion.execution.time_zone + +########## +## SECTION 5: AT TIME ZONE +########## + +# 5a. AT TIME ZONE applied to a tz-NAIVE value. +# DataFusion reinterprets the wall clock as being in the named zone and returns +# a tz-aware value. This agrees with PostgreSQL, which also returns +# `timestamp with time zone` here: PostgreSQL renders the same instant as +# `2024-07-01 18:00:00+00` under TimeZone='UTC'. +query TP +SELECT arrow_typeof(TIMESTAMP '2024-07-01 12:00:00' AT TIME ZONE 'America/Denver'), + TIMESTAMP '2024-07-01 12:00:00' AT TIME ZONE 'America/Denver' +---- +Timestamp(ns, "America/Denver") 2024-07-01T12:00:00-06:00 + +# DIVERGES FROM POSTGRESQL (instant). With a fixed-offset *string* the two +# engines use opposite sign conventions: DataFusion reads `'+05:30'` as +# UTC+05:30, so the value below is 06:30 UTC, while PostgreSQL reads the string +# POSIX-style (west positive) and answers `2024-07-01 17:30:00+00`. PostgreSQL's +# `AT TIME ZONE INTERVAL '05:30'` matches DataFusion instead. The same +# convention applies on a real column further down (5c). +# See https://github.com/apache/datafusion/issues/25170 +query TP +SELECT arrow_typeof(TIMESTAMP '2024-07-01 12:00:00' AT TIME ZONE '+05:30'), + TIMESTAMP '2024-07-01 12:00:00' AT TIME ZONE '+05:30' +---- +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 +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 + +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 + +# 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 +query P +SELECT ('2024-07-01T18:00:00Z'::timestamptz AT TIME ZONE 'America/Denver')::timestamp +---- +2024-07-02T00:00:00 + +# 5c. AT TIME ZONE on real naive columns (not const folded) +statement ok +CREATE TABLE naive_col AS VALUES ('2024-07-01 12:00:00'::timestamp), ('2024-01-15 12:00:00'::timestamp) + +query TP rowsort +SELECT arrow_typeof(column1 AT TIME ZONE 'America/Denver'), + column1 AT TIME ZONE 'America/Denver' +FROM naive_col +---- +Timestamp(ns, "America/Denver") 2024-01-15T12:00:00-07:00 +Timestamp(ns, "America/Denver") 2024-07-01T12:00:00-06:00 + +# DIVERGES FROM POSTGRESQL: DataFusion reads the offset string '+05:30' as east +# of UTC (ISO 8601). PostgreSQL reads a bare offset string POSIX-style, as west +# of UTC, and DuckDB rejects it. +# See https://github.com/apache/datafusion/issues/25170 +query TP rowsort +SELECT arrow_typeof(column1 AT TIME ZONE '+05:30'), + column1 AT TIME ZONE '+05:30' +FROM naive_col +---- +Timestamp(ns, "+05:30") 2024-01-15T12:00:00+05:30 +Timestamp(ns, "+05:30") 2024-07-01T12:00:00+05:30 + +########## +## SECTION 6: casts in all four directions +########## + +# 6a. naive -> named: the wall clock is reinterpreted in the target zone +query TP rowsort +SELECT arrow_typeof(arrow_cast(column1, 'Timestamp(Nanosecond, Some("America/Denver"))')), + arrow_cast(column1, 'Timestamp(Nanosecond, Some("America/Denver"))') +FROM naive_col +---- +Timestamp(ns, "America/Denver") 2024-01-15T12:00:00-07:00 +Timestamp(ns, "America/Denver") 2024-07-01T12:00:00-06:00 + +# 6b. named -> naive +# +# DIVERGES FROM POSTGRESQL. DataFusion always yields the *UTC* wall clock and +# ignores `datafusion.execution.time_zone` entirely (see SECTION 7 for the same +# cast under a non-UTC session zone). PostgreSQL yields the wall clock in the +# session `TimeZone`: under TimeZone='America/Denver' the value below reads +# `2024-07-01 12:00:00`, not `2024-07-01 18:00:00`. +# See https://github.com/apache/datafusion/issues/12218 +query TP rowsort +SELECT arrow_typeof(column1::timestamp), column1::timestamp FROM c_denver +---- +Timestamp(ns) 2024-01-15T19:00:00 +Timestamp(ns) 2024-07-01T18:00:00 + +# 6c. named -> other named: instant preserving +query TP rowsort +SELECT arrow_typeof(arrow_cast(column1, 'Timestamp(Nanosecond, Some("Europe/Brussels"))')), + arrow_cast(column1, 'Timestamp(Nanosecond, Some("Europe/Brussels"))') +FROM c_denver +---- +Timestamp(ns, "Europe/Brussels") 2024-01-15T20:00:00+01:00 +Timestamp(ns, "Europe/Brussels") 2024-07-01T20:00:00+02:00 + +# 6d. naive -> fixed offset +query TP rowsort +SELECT arrow_typeof(arrow_cast(column1, 'Timestamp(Nanosecond, Some("+05:30"))')), + arrow_cast(column1, 'Timestamp(Nanosecond, Some("+05:30"))') +FROM naive_col +---- +Timestamp(ns, "+05:30") 2024-01-15T12:00:00+05:30 +Timestamp(ns, "+05:30") 2024-07-01T12:00:00+05:30 + +# 6e. named -> fixed offset +query TP rowsort +SELECT arrow_typeof(arrow_cast(column1, 'Timestamp(Nanosecond, Some("+05:30"))')), + arrow_cast(column1, 'Timestamp(Nanosecond, Some("+05:30"))') +FROM c_denver +---- +Timestamp(ns, "+05:30") 2024-01-16T00:30:00+05:30 +Timestamp(ns, "+05:30") 2024-07-01T23:30:00+05:30 + +########## +## SECTION 7: round trips +########## + +statement ok +SET datafusion.execution.time_zone = 'America/Denver' + +# naive -> aware -> naive. +# +# KNOWN WRONG. The round trip is not the identity under a non-UTC session zone: +# `::timestamptz` interprets the naive value in the session zone (Denver) and +# `::timestamp` then renders it in UTC, so the value moves by the offset. +# PostgreSQL round trips exactly, because both halves use the session TimeZone. +# See https://github.com/apache/datafusion/issues/12218 +query PP rowsort +SELECT column1 AS orig, column1::timestamptz::timestamp AS roundtrip FROM naive_col +---- +2024-01-15T12:00:00 2024-01-15T19:00:00 +2024-07-01T12:00:00 2024-07-01T18:00:00 + +query B rowsort +SELECT column1 = column1::timestamptz::timestamp AS eq FROM naive_col +---- +false +false + +# aware -> naive -> aware, same problem in the other direction. +# See https://github.com/apache/datafusion/issues/12218 +query PP rowsort +SELECT column1 AS orig, column1::timestamp::timestamptz AS roundtrip FROM c_denver +---- +2024-01-15T12:00:00-07:00 2024-01-15T19:00:00-07:00 +2024-07-01T12:00:00-06:00 2024-07-01T18:00:00-06:00 + +query B rowsort +SELECT column1 = column1::timestamp::timestamptz AS eq FROM c_denver +---- +false +false + +# The round trip *is* the identity when the session zone is UTC +statement ok +SET datafusion.execution.time_zone = '+00:00' + +query B rowsort +SELECT column1 = column1::timestamptz::timestamp AS eq FROM naive_col +---- +true +true + +statement ok +RESET datafusion.execution.time_zone + +########## +## SECTION 8: comparison and equality across tz-aware and tz-naive +## +## The pins here record two behaviours. A tz-naive side is read in the other +## operand's zone, where PostgreSQL uses the session zone: +## https://github.com/apache/datafusion/issues/13212 +## And `'...Z'::timestamptz` is tz-naive while the session zone is unset +## (SECTION 0): https://github.com/apache/datafusion/issues/25166 +## +## The unwrap_cast bug, https://github.com/apache/datafusion/issues/25095, needs an explicit +## CAST of a tz-naive column compared against a literal under a non-UTC session +## zone. No query in this section has that shape. +########## + +statement ok +CREATE TABLE cmp_utc AS +SELECT arrow_cast(column1, 'Timestamp(Nanosecond, Some("+00:00"))') AS ts +FROM (VALUES ('2024-07-01T00:00:00Z'), ('2024-07-01T06:00:00Z'), ('2024-07-01T12:00:00Z'), ('2024-07-01T18:00:00Z')) + +statement ok +CREATE TABLE cmp_denver AS +SELECT arrow_cast(column1, 'Timestamp(Nanosecond, Some("America/Denver"))') AS ts +FROM (VALUES ('2024-07-01T00:00:00Z'), ('2024-07-01T06:00:00Z'), ('2024-07-01T12:00:00Z'), ('2024-07-01T18:00:00Z')) + +query TP rowsort +SELECT arrow_typeof(ts), ts FROM cmp_utc +---- +Timestamp(ns, "+00:00") 2024-07-01T00:00:00Z +Timestamp(ns, "+00:00") 2024-07-01T06:00:00Z +Timestamp(ns, "+00:00") 2024-07-01T12:00:00Z +Timestamp(ns, "+00:00") 2024-07-01T18:00:00Z + +query TP rowsort +SELECT arrow_typeof(ts), ts FROM cmp_denver +---- +Timestamp(ns, "America/Denver") 2024-06-30T18:00:00-06:00 +Timestamp(ns, "America/Denver") 2024-07-01T00:00:00-06:00 +Timestamp(ns, "America/Denver") 2024-07-01T06:00:00-06:00 +Timestamp(ns, "America/Denver") 2024-07-01T12:00:00-06:00 + +# tz-aware column compared to a tz-naive literal, session tz unset. +# +# DIVERGES FROM POSTGRESQL. DataFusion coerces the naive literal into the +# *column's* time zone, so `'2024-07-01 12:00:00'` means noon in Denver when +# compared against a Denver column. PostgreSQL coerces the naive literal using +# the *session* TimeZone, so under TimeZone='UTC' the same literal means noon +# UTC and a different row matches. The two engines only agree when the session +# zone happens to equal the column's zone. +query P rowsort +SELECT ts FROM cmp_utc WHERE ts = '2024-07-01 12:00:00' +---- +2024-07-01T12:00:00Z + +query P rowsort +SELECT ts FROM cmp_utc WHERE ts > '2024-07-01 06:00:00' +---- +2024-07-01T12:00:00Z +2024-07-01T18:00:00Z + +# CONCRETE DIVERGENCE. DataFusion returns the row whose *Denver-local* reading +# is noon, i.e. the 18:00Z instant (rendered below as 12:00:00-06:00). +# PostgreSQL under TimeZone='UTC' reads the literal as noon UTC and returns the +# 12:00Z instant instead -- a different row for the same query text. +query P rowsort +SELECT ts FROM cmp_denver WHERE ts = '2024-07-01 12:00:00' +---- +2024-07-01T12:00:00-06:00 + +# DataFusion returns ONE row; PostgreSQL under TimeZone='UTC' returns TWO +# (12:00Z and 18:00Z), because it reads the bare literal as 06:00 UTC rather +# than 06:00 Denver. +query P rowsort +SELECT ts FROM cmp_denver WHERE ts > '2024-07-01 06:00:00' +---- +2024-07-01T12:00:00-06:00 + +# The same comparisons under a non-UTC session time zone. Note that the results +# are IDENTICAL to the ones above: `datafusion.execution.time_zone` has no +# effect on how a naive literal is coerced for comparison against a tz-aware +# column. In PostgreSQL, changing TimeZone changes which rows match. +statement ok +SET datafusion.execution.time_zone = 'America/Denver' + +query P rowsort +SELECT ts FROM cmp_utc WHERE ts = '2024-07-01 12:00:00' +---- +2024-07-01T12:00:00Z + +query P rowsort +SELECT ts FROM cmp_denver WHERE ts = '2024-07-01 12:00:00' +---- +2024-07-01T12:00:00-06:00 + +query P rowsort +SELECT ts FROM cmp_denver WHERE ts > '2024-07-01 06:00:00' +---- +2024-07-01T12:00:00-06:00 + +statement ok +RESET datafusion.execution.time_zone + +# tz-naive column compared to a tz-aware literal +query P rowsort +SELECT column1 FROM naive_col WHERE column1 = '2024-07-01T12:00:00Z'::timestamptz +---- +2024-07-01T12:00:00 + +# DIVERGES FROM POSTGRESQL: the tz-naive literal is read in the column's zone +# (America/Denver), so it matches the July row. PostgreSQL reads a tz-naive +# value in the session zone instead. +# See https://github.com/apache/datafusion/issues/13212 +query B rowsort +SELECT column1 = TIMESTAMP '2024-07-01 12:00:00' AS eq FROM c_denver +---- +false +true + +# KNOWN WRONG, and the sharpest form of the problem: with the session zone +# unset, `'...Z'::timestamptz` is tz-naive (SECTION 0), so when it is then +# compared against a zoned column the explicit `Z` in the literal is +# effectively discarded and the wall clock is re-read in the column's zone. +# The query below asks for the row at 06:00 UTC and gets the row at 12:00 UTC. +# PostgreSQL returns the 06:00Z row. See +# https://github.com/apache/datafusion/issues/25166 +query P +SELECT ts FROM cmp_denver WHERE ts = '2024-07-01T06:00:00Z'::timestamptz +---- +2024-07-01T06:00:00-06:00 + +# Setting a session zone makes the literal tz-aware and the same query correct +statement ok +SET datafusion.execution.time_zone = '+00:00' + +query P +SELECT ts FROM cmp_denver WHERE ts = '2024-07-01T06:00:00Z'::timestamptz +---- +2024-07-01T00:00:00-06:00 + +statement ok +RESET datafusion.execution.time_zone + +# EXPLAIN of the pushdown path, so that any optimizer change to the +# cast/comparison rewrite (unwrap_cast, simplify_expressions) shows up here. +statement ok +set datafusion.explain.logical_plan_only = true + +query TT +EXPLAIN SELECT ts FROM cmp_denver WHERE ts = '2024-07-01 12:00:00' +---- +logical_plan +01)Filter: cmp_denver.ts = TimestampNanosecond(1719856800000000000, Some("America/Denver")) +02)--TableScan: cmp_denver projection=[ts] + +query TT +EXPLAIN SELECT ts FROM cmp_denver WHERE ts > TIMESTAMP '2024-07-01 06:00:00' +---- +logical_plan +01)Filter: cmp_denver.ts > TimestampNanosecond(1719835200000000000, Some("America/Denver")) +02)--TableScan: cmp_denver projection=[ts] + +query TT +EXPLAIN SELECT ts FROM cmp_utc WHERE ts < '2024-07-01T18:00:00Z'::timestamptz +---- +logical_plan +01)Filter: cmp_utc.ts < TimestampNanosecond(1719856800000000000, Some("+00:00")) +02)--TableScan: cmp_utc projection=[ts] + +# The literal is folded into the column's time zone rather than being left as a +# cast on the column. Under a Denver session zone the folded constant is the +# same as above, confirming the session zone is ignored on this path. +statement ok +SET datafusion.execution.time_zone = 'America/Denver' + +query TT +EXPLAIN SELECT ts FROM cmp_utc WHERE ts = '2024-07-01 12:00:00' +---- +logical_plan +01)Filter: cmp_utc.ts = TimestampNanosecond(1719835200000000000, Some("+00:00")) +02)--TableScan: cmp_utc projection=[ts] + +statement ok +RESET datafusion.execution.time_zone + +statement ok +set datafusion.explain.logical_plan_only = false + +########## +## SECTION 9: date_bin / date_trunc / date_part on tz-aware input +########## + +statement ok +CREATE TABLE day_denver AS +SELECT arrow_cast(column1, 'Timestamp(Nanosecond, Some("America/Denver"))') AS ts +FROM (VALUES ('2024-07-01T00:00:00Z'), ('2024-07-01T06:00:00Z'), ('2024-07-01T12:00:00Z'), ('2024-07-01T18:00:00Z')) + +# KNOWN INCONSISTENCY (internal to DataFusion): `date_bin` bins on the +# underlying UTC instant, while `date_trunc` truncates in the value's own time +# zone. For the same input they therefore return different answers, even though +# both return a value typed in the column's zone. +# +# PostgreSQL's `date_bin` also bins on the instant relative to the supplied +# origin, so DataFusion's `date_bin` agrees with it. PostgreSQL's `date_trunc` +# truncates in the *session* TimeZone (or an explicit third argument), not in +# the value's zone, so `date_trunc` is where the two engines part company. +# See https://github.com/apache/datafusion/issues/25167 +query PP rowsort +SELECT date_bin(INTERVAL '1 day', ts), date_trunc('day', ts) FROM day_denver +---- +2024-06-30T18:00:00-06:00 2024-06-30T00:00:00-06:00 +2024-06-30T18:00:00-06:00 2024-07-01T00:00:00-06:00 +2024-06-30T18:00:00-06:00 2024-07-01T00:00:00-06:00 +2024-06-30T18:00:00-06:00 2024-07-01T00:00:00-06:00 + +query B rowsort +SELECT date_bin(INTERVAL '1 day', ts) = date_trunc('day', ts) AS agree FROM day_denver +---- +false +false +false +false + +query TT +SELECT arrow_typeof(date_bin(INTERVAL '1 day', ts)), arrow_typeof(date_trunc('day', ts)) FROM day_denver LIMIT 1 +---- +Timestamp(ns, "America/Denver") Timestamp(ns, "America/Denver") + +# The same disagreement, but sharper, in a zone whose offset is not a whole +# multiple of the stride. In Denver both functions at least return *a* +# midnight: `date_trunc` local midnight, `date_bin` UTC midnight rendered as +# 18:00 local. Asia/Kolkata is UTC+05:30, so `date_bin` returns 05:30 local -- +# not a day boundary in the zone the value is typed as, and therefore a +# GROUP BY key that lines up with no calendar the user asked for. +# See https://github.com/apache/datafusion/issues/25167 +query PPP +SELECT arrow_cast(TIMESTAMP '2024-01-01 12:00:00', 'Timestamp(Second, Some("Asia/Kolkata"))') AS t, + date_trunc('day', arrow_cast(TIMESTAMP '2024-01-01 12:00:00', 'Timestamp(Second, Some("Asia/Kolkata"))')) AS dtrunc, + date_bin(INTERVAL '1 day', arrow_cast(TIMESTAMP '2024-01-01 12:00:00', 'Timestamp(Second, Some("Asia/Kolkata"))')) AS dbin +---- +2024-01-01T12:00:00+05:30 2024-01-01T00:00:00+05:30 2024-01-01T05:30:00+05:30 + +# At hour granularity the two agree, because the Denver offset is a whole hour +query PP rowsort +SELECT date_bin(INTERVAL '1 hour', ts), date_trunc('hour', ts) FROM day_denver +---- +2024-06-30T18:00:00-06:00 2024-06-30T18:00:00-06:00 +2024-07-01T00:00:00-06:00 2024-07-01T00:00:00-06:00 +2024-07-01T06:00:00-06:00 2024-07-01T06:00:00-06:00 +2024-07-01T12:00:00-06:00 2024-07-01T12:00:00-06:00 + +# date_bin bins on the UTC instant and date_trunc truncates in the value's zone, +# so their month boundaries differ. See +# https://github.com/apache/datafusion/issues/25167 +query PP rowsort +SELECT date_bin(INTERVAL '1 month', ts), date_trunc('month', ts) FROM day_denver +---- +2024-06-30T18:00:00-06:00 2024-06-01T00:00:00-06:00 +2024-06-30T18:00:00-06:00 2024-07-01T00:00:00-06:00 +2024-06-30T18:00:00-06:00 2024-07-01T00:00:00-06:00 +2024-06-30T18:00:00-06:00 2024-07-01T00:00:00-06:00 + +# date_bin with an explicit tz-aware origin +query P rowsort +SELECT date_bin(INTERVAL '1 day', ts, arrow_cast('2024-01-01T00:00:00Z', 'Timestamp(Nanosecond, Some("America/Denver"))')) FROM day_denver +---- +2024-06-30T18:00:00-06:00 +2024-06-30T18:00:00-06:00 +2024-06-30T18:00:00-06:00 +2024-06-30T18:00:00-06:00 + +# NOT A DIVERGENCE, but a trap. An explicit origin looks like a way to align +# bins to local midnight, and it is -- until the zone's offset changes. +# `date_bin` steps a fixed number of nanoseconds from a fixed instant, so it +# cannot track a local day that is 23 or 25 hours long. The two rows below are +# the same local time of day on either side of the America/Denver +# spring-forward transition, and the origin is chosen so that bins land on +# local midnight. +# +# PostgreSQL 15 (`date_bin`) and DuckDB 1.5.2 (`time_bucket` with an origin) +# return exactly the same values, so this is inherent to instant-based binning. +# `date_trunc` is included as a control: it gets both rows right. +# See https://github.com/apache/datafusion/issues/25168 +statement ok +CREATE TABLE dst_origin AS Review Comment: Small cleanup suggestion: `dst_origin` is created here but isn't dropped in the cleanup block. The per-file context makes this harmless today, but could we add `DROP TABLE dst_origin` with the other cleanup statements? That would keep the suite self-contained if the context lifetime ever changes. -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
