Skip to content

Commit a321213

Browse files
adriangbclaude
andcommitted
test: pin the date_bin explicit-origin DST drift
Section 9 already had a `date_bin(..., origin)` case, but all of its data sits inside a single July day, so it never crosses a transition and the drift is invisible. Adds a case whose two rows are the same local time of day on either side of the America/Denver spring-forward transition, with the origin chosen so bins land on local midnight. The first row does; the second lands on 01:00 local. That is the bug in apache#25168 -- `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. `date_trunc` is asserted alongside as a control: it gets both rows right, so the drift is a property of the origin arithmetic rather than of the data. Co-Authored-By: Claude Opus 5 <[email protected]>
1 parent 920c73f commit a321213

1 file changed

Lines changed: 35 additions & 0 deletions

File tree

‎datafusion/sqllogictest/test_files/datetime/timestamps_timezone.slt‎

Lines changed: 35 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -730,6 +730,41 @@ SELECT date_bin(INTERVAL '1 day', ts, arrow_cast('2024-01-01T00:00:00Z', 'Timest
730730
2024-06-30T18:00:00-06:00
731731
2024-06-30T18:00:00-06:00
732732

733+
# KNOWN BUG: an explicit origin looks like a way to align bins to local
734+
# midnight, and it is -- until the zone's offset changes. `date_bin` steps a
735+
# fixed number of nanoseconds from a fixed instant, so it cannot track a local
736+
# day that is 23 or 25 hours long. The two rows below are the same local time
737+
# of day on either side of the America/Denver spring-forward transition, and
738+
# the origin is chosen so that bins land on local midnight.
739+
#
740+
# `date_trunc` is included as a control: it gets both rows right, so the drift
741+
# is a property of the origin arithmetic and not of the data.
742+
# See https://github.com/apache/datafusion/issues/25168
743+
statement ok
744+
CREATE TABLE dst_origin AS
745+
SELECT arrow_cast(column1, 'Timestamp(Nanosecond, Some("America/Denver"))') AS ts
746+
FROM (VALUES ('2024-03-09T13:30:00Z'), ('2024-03-11T12:30:00Z'))
747+
748+
query PPP
749+
SELECT ts,
750+
date_bin(INTERVAL '1 day', ts, arrow_cast('2024-03-01T07:00:00Z', 'Timestamp(Nanosecond, Some("UTC"))')),
751+
date_trunc('day', ts)
752+
FROM dst_origin ORDER BY ts
753+
----
754+
2024-03-09T06:30:00-07:00 2024-03-09T00:00:00-07:00 2024-03-09T00:00:00-07:00
755+
2024-03-11T06:30:00-06:00 2024-03-11T01:00:00-06:00 2024-03-11T00:00:00-06:00
756+
757+
# The second row is the bug: 01:00 local, not midnight, one hour after the
758+
# transition. Use the `to_local_time` idiom instead of an origin when you need
759+
# local calendar bins.
760+
query B
761+
SELECT date_bin(INTERVAL '1 day', ts, arrow_cast('2024-03-01T07:00:00Z', 'Timestamp(Nanosecond, Some("UTC"))'))
762+
= date_trunc('day', ts) AS origin_matches_local_midnight
763+
FROM dst_origin ORDER BY ts
764+
----
765+
true
766+
false
767+
733768
# date_part reads the field in the value's own time zone. This matches
734769
# PostgreSQL whenever the session TimeZone equals the column's zone.
735770
query IIR rowsort

0 commit comments

Comments
 (0)