kosiew commented on code in PR #25165: URL: https://github.com/apache/datafusion/pull/25165#discussion_r4082033951
########## datafusion/sqllogictest/test_files/datetime/timestamps.slt: ########## @@ -4049,6 +4049,120 @@ SELECT '2000-12-01 04:04:12' AT TIME ZONE 'America/New York'; statement error SELECT '2023-03-12 02:00:00' AT TIME ZONE 'EDT'; +########## +## AT TIME ZONE applied to a timezone-*aware* timestamp +## +## https://github.com/apache/datafusion/issues/12218 +## +## PostgreSQL (and DuckDB) semantics are asymmetric: +## +## * `<tz-naive timestamp> AT TIME ZONE zone` reads the value as a wall clock +## in `zone` and returns the matching tz-*aware* instant, and +## * `<tz-aware timestamp> AT TIME ZONE zone` returns the wall clock that the +## instant has in `zone`, as a tz-*naive* timestamp. +## +## Every result below was checked against PostgreSQL 17. +########## + +statement ok +SET datafusion.execution.time_zone = 'UTC'; + +statement ok +CREATE TABLE at_tz_t AS +SELECT + '2024-01-01T12:00:00Z'::timestamptz AS tstz, + '2024-01-01 12:00:00'::timestamp AS tsn; + +# The tz-naive column is unaffected by this issue: noon-in-Denver is the same +# instant PostgreSQL reports (`2024-01-01 19:00:00+00`). +query TP +SELECT arrow_typeof(tsn AT TIME ZONE 'America/Denver'), tsn AT TIME ZONE 'America/Denver' FROM at_tz_t; +---- +Timestamp(ns, "America/Denver") 2024-01-01T12:00:00-07:00 + +# The tz-aware column: PostgreSQL returns `timestamp` (naive) `2024-01-01 05:00:00`. +query TP +SELECT arrow_typeof(tstz AT TIME ZONE 'America/Denver'), tstz AT TIME ZONE 'America/Denver' FROM at_tz_t; Review Comment: Small test coverage suggestion: could we add cases for both `NULL::timestamp AT TIME ZONE ...` and `NULL::timestamptz AT TIME ZONE ...`, checking both `arrow_typeof` and the NULL value? The dispatch now depends on `Expr::get_type`, while `to_local_time` has separate null scalar handling. A couple of SQL-level tests here would help guard the interaction between those paths. This is non-blocking. -- 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]
