[
https://issues.apache.org/jira/browse/SPARK-57769?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Deepayan Patra updated SPARK-57769:
-----------------------------------
Description:
At a daylight-saving fall-back transition a wall-clock local time occurs twice
(once before and once after the clocks are turned back). When {{date_trunc}}
with a date-level unit (WEEK / MONTH / QUARTER / YEAR) produces a truncated
local midnight that lands on such an overlap, that midnight maps to two valid
instants and either is a correct representation.
Since SPARK-56769 added the offset-arithmetic fast path for these units, the
truncated midnight is resolved using the offset of the source timestamp. That
is a valid choice, but it makes the result depend on the source offset: two
timestamps in the same period can truncate to different instants when one of
them sits on the overlap, so {{GROUP BY date_trunc(...)}} may place them in
different groups. Some workloads prefer the alternative, offset-independent
resolution that always picks the earliest valid offset (the pre-SPARK-56769 /
slow-path result), which is deterministic per period.
This adds an internal config to let users choose. Default {{false}} keeps the
current behavior (no change); {{true}} routes date-level truncations through
the slow path so the earliest valid offset is always used. Both results are
correct representations of the overlapped midnight; the config only controls
which one is returned.
h3. Example
Session time zone {{Europe/Berlin}} (Germany's first DST ended 1916-10-01 01:00
CEST -> 00:00 CET, so local {{1916-10-01 00:00}} exists at both +02:00 CEST and
+01:00 CET):
{code:sql}
SET spark.sql.session.timeZone = 'Europe/Berlin';
SELECT date_trunc('MONTH', TIMESTAMP '1916-10-15 12:00:00');
-- default (false) -> 1916-09-30 23:00:00Z (source offset, +01:00 CET)
-- config = true -> 1916-09-30 22:00:00Z (earliest valid offset, +02:00
CEST)
{code}
{{Atlantic/Azores}} 1912-01-01 (LMT -01:54:32 -> -02:00) is another instance, a
328-second difference.
was:
SPARK-56769 (extending SPARK-56663) added an offset-arithmetic fast path to
{{DateTimeUtils.truncTimestamp}} for the date-level units WEEK / MONTH /
QUARTER / YEAR. The fast path resolves the truncated local-midnight boundary
back to UTC using the offset of the *source* timestamp.
At a daylight-saving fall-back transition the truncated local midnight occurs
twice (once before and once after the clocks are turned back). The slow-path
reference ({{daysToMicros}}) resolves such a midnight with the *earliest* valid
offset, while the fast path reuses the source timestamp's offset. They
therefore disagree whenever the truncated boundary lands on a fall-back overlap:
# The fast path returns a different value than its own slow-path reference at
these instants.
# {{date_trunc}} becomes dependent on the source offset: two timestamps in the
same period can truncate to different instants when one of them sits on the
overlap, which silently breaks {{GROUP BY date_trunc(...)}}.
h3. Reproduction
Session time zone {{Europe/Berlin}} (Germany's first DST ended 1916-10-01 01:00
CEST -> 00:00 CET, so local {{1916-10-01 00:00}} exists at both +02:00 CEST and
+01:00 CET):
{code:sql}
SET spark.sql.session.timeZone = 'Europe/Berlin';
-- Source offset is +01:00 CET (well after the transition):
SELECT date_trunc('MONTH', TIMESTAMP '1916-10-15 12:00:00');
-- fast path -> 1916-09-30 23:00:00Z (source offset, +01:00)
-- reference -> 1916-09-30 22:00:00Z (earliest valid offset, +02:00)
{code}
A source sitting in the overlap (e.g. {{1916-10-01 00:30}} at +02:00) truncates
to {{1916-09-30 22:00:00Z}} under both paths, so the two October rows disagree.
{{Atlantic/Azores}} 1912-01-01 (LMT -01:54:32 -> -02:00) is another instance, a
328-second difference.
h3. Proposed fix
Add an internal config
{{spark.sql.legacy.timestampTruncateToOverlapEarliestOffset}} (default
{{false}}, keeping the current fast-path behavior). When {{true}}, date-level
truncations are routed through the slow path so the earliest valid offset is
always used, making the result independent of the source offset and therefore
deterministic per period.
Issue Type: Improvement (was: Bug)
Summary: Add a config to use the earliest offset for date_trunc at a
DST fall-back overlap (was: date_trunc fast path diverges from its slow-path
reference at a DST fall-back overlap)
> Add a config to use the earliest offset for date_trunc at a DST fall-back
> overlap
> ---------------------------------------------------------------------------------
>
> Key: SPARK-57769
> URL: https://issues.apache.org/jira/browse/SPARK-57769
> Project: Spark
> Issue Type: Improvement
> Components: SQL
> Affects Versions: 4.3.0
> Reporter: Deepayan Patra
> Priority: Major
> Labels: correctness
>
> At a daylight-saving fall-back transition a wall-clock local time occurs
> twice (once before and once after the clocks are turned back). When
> {{date_trunc}} with a date-level unit (WEEK / MONTH / QUARTER / YEAR)
> produces a truncated local midnight that lands on such an overlap, that
> midnight maps to two valid instants and either is a correct representation.
> Since SPARK-56769 added the offset-arithmetic fast path for these units, the
> truncated midnight is resolved using the offset of the source timestamp. That
> is a valid choice, but it makes the result depend on the source offset: two
> timestamps in the same period can truncate to different instants when one of
> them sits on the overlap, so {{GROUP BY date_trunc(...)}} may place them in
> different groups. Some workloads prefer the alternative, offset-independent
> resolution that always picks the earliest valid offset (the pre-SPARK-56769 /
> slow-path result), which is deterministic per period.
> This adds an internal config to let users choose. Default {{false}} keeps the
> current behavior (no change); {{true}} routes date-level truncations through
> the slow path so the earliest valid offset is always used. Both results are
> correct representations of the overlapped midnight; the config only controls
> which one is returned.
> h3. Example
> Session time zone {{Europe/Berlin}} (Germany's first DST ended 1916-10-01
> 01:00 CEST -> 00:00 CET, so local {{1916-10-01 00:00}} exists at both +02:00
> CEST and +01:00 CET):
> {code:sql}
> SET spark.sql.session.timeZone = 'Europe/Berlin';
> SELECT date_trunc('MONTH', TIMESTAMP '1916-10-15 12:00:00');
> -- default (false) -> 1916-09-30 23:00:00Z (source offset, +01:00 CET)
> -- config = true -> 1916-09-30 22:00:00Z (earliest valid offset, +02:00
> CEST)
> {code}
> {{Atlantic/Azores}} 1912-01-01 (LMT -01:54:32 -> -02:00) is another instance,
> a 328-second difference.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
