[jira] [Updated] (SPARK-57769) Add a config to use the earliest offset for date_trunc at a DST fall-back overlap

["Deepayan Patra (Jira)" <ji...@apache.org>]

     [ 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: issues-unsubscribe@spark.apache.org
For additional commands, e-mail: issues-help@spark.apache.org