andygrove opened a new issue, #110: URL: https://github.com/apache/datafusion-spark/issues/110
### Describe the bug `to_utc_timestamp` gives wrong results for local times near a daylight saving time change. [to_utc_timestamp.rs#L191](https://github.com/apache/datafusion-spark/blob/main/crates/spark/src/function/datetime/to_utc_timestamp.rs#L191) looks up the zone's offset with `offset_from_utc_datetime`, which treats the input wall-clock time as if it were already UTC. The input is a local time in the given zone, so the offset should come from resolving that local time (`offset_from_local_datetime`), with Spark's rules for gaps and overlaps. Within a few hours of a change, the offset taken is the one on the wrong side of it. `from_utc_timestamp` does not have this problem; its results match Spark for the same inputs. ### To Reproduce Session time zone UTC, zone `America/New_York`: | input | datafusion-spark | Spark 4.2.0 | |---|---|---| | `2024-03-10 01:30:00` | `2024-03-10 06:30:00` | `2024-03-10 06:30:00` | | `2024-03-10 02:30:00` (in the gap) | `2024-03-10 07:30:00` | `2024-03-10 07:30:00` | | `2024-03-10 03:30:00` | `2024-03-10 08:30:00` | `2024-03-10 07:30:00` | | `2024-11-03 00:30:00` | `2024-11-03 04:30:00` | `2024-11-03 04:30:00` | | `2024-11-03 01:30:00` (in the overlap) | `2024-11-03 05:30:00` | `2024-11-03 05:30:00` | | `2024-11-03 02:30:00` | `2024-11-03 06:30:00` | `2024-11-03 07:30:00` | ```sql SELECT to_utc_timestamp(TIMESTAMP '2024-03-10 03:30:00', 'America/New_York'); SELECT to_utc_timestamp(TIMESTAMP '2024-11-03 02:30:00', 'America/New_York'); ``` ### Expected behavior Resolve the input as a local time in the given zone, and handle a time in a gap or an overlap the way Spark does (for the gap, Spark moves forward by the length of the gap; for the overlap, it takes the earlier offset), so results match Spark around every DST change. ### Additional context Spark results come from PySpark 4.2.0 with `spark.sql.session.timeZone=UTC`, read with `CAST(... AS STRING)`. -- 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]
