andygrove opened a new issue, #233:
URL: https://github.com/apache/datafusion-spark/issues/233

   Part of #175 and apache/datafusion-comet#7300.
   
   ### What the function does
   
   `convert_timezone([sourceTz, ]targetTz, sourceTs)` treats `sourceTs` as a 
wall-clock time in `sourceTz`, and returns the
   wall-clock time of the same instant in `targetTz`. With two arguments, 
`sourceTz` is the session time zone
   (`current_timezone()`, i.e. `spark.sql.session.timeZone`).
   
   The time zone arguments are `STRING`; `sourceTs` is implicitly cast to 
`TIMESTAMP_NTZ` (`ImplicitCastInputTypes`), so a
   `TIMESTAMP` (LTZ) argument is first converted to its local time in the 
session time zone, and `DATE` / `STRING`
   arguments go through the usual casts. The result is always `TIMESTAMP_NTZ`. 
NULL in any argument gives NULL. Available
   since 3.4.0; no aliases.
   
   ### Examples
   
   Session time zone `America/Los_Angeles` (as in Spark's docs and golden 
files):
   
   ```sql
   SELECT convert_timezone('Europe/Brussels', 'America/Los_Angeles', 
timestamp_ntz'2021-12-06 00:00:00');
   -- 2021-12-05 15:00:00
   SELECT convert_timezone('Europe/Brussels', timestamp_ntz'2021-12-05 
15:00:00');
   -- 2021-12-06 00:00:00
   SELECT convert_timezone('Europe/Moscow', 'America/Los_Angeles', 
timestamp_ntz'2022-01-01 00:00:00');
   -- 2021-12-31 13:00:00
   SELECT convert_timezone('Europe/Brussels', timestamp_ntz'2022-03-23 
00:00:00');
   -- 2022-03-23 08:00:00
   -- An LTZ input is cast to NTZ in the session zone first:
   SELECT convert_timezone('Europe/Brussels', timestamp_ltz'2022-03-23 00:00:00 
America/Los_Angeles');
   -- 2022-03-23 08:00:00
   -- Right after the spring-forward transition in Brussels 
(DateExpressionsSuite):
   SELECT convert_timezone('Europe/Brussels', 'Europe/Moscow', 
timestamp_ntz'2022-03-27 03:00:00');
   -- 2022-03-27 04:00:00
   SELECT convert_timezone('America/Los_Angeles', 'UTC', CAST(NULL AS 
TIMESTAMP_NTZ));
   -- NULL
   ```
   
   ### Edge cases to test
   
   - NULL `sourceTz`, NULL `targetTz`, NULL `sourceTs` (each gives NULL).
   - DST in the source zone, which uses `LocalDateTime.atZone` rules: a 
wall-clock time in a gap
     (`2022-03-27 02:30` in `Europe/Brussels`) is moved forward by the gap 
length (to `03:30` CEST); a time in an overlap
     (`2022-10-30 02:30` in `Europe/Brussels`) uses the earlier offset (CEST, 
+02:00). DST in the target zone falls out of
     the instant-to-local conversion.
   - Two-argument form uses the session zone as source, and changes when 
`spark.sql.session.timeZone` changes.
   - `TIMESTAMP` (LTZ) input: implicit cast to NTZ uses the session zone, so 
the result depends on it twice.
   - `DATE` input (midnight) and `STRING` input (cast to `TIMESTAMP_NTZ`; 
invalid strings follow cast rules, i.e. NULL with
     ANSI off and `CAST_INVALID_INPUT` with ANSI on).
   - Zone spellings for both arguments: region IDs, `UTC`, `Z`, `+01:00`, 
`-08`, `GMT+1`, `UTC+01:00`, `ZoneId.SHORT_IDS`
     (`PST`, `CET`, `EST` which is a fixed -05:00), legacy `+h:mm` / `+hh:m`. 
Unknown zone: `INVALID_TIMEZONE` (SQLSTATE
     22009) in 4.x, independent of ANSI mode.
   - Microsecond precision is preserved; negative (pre-1970) values and years 
before 1582 (proleptic Gregorian, plus
     historical LMT offsets in the tz database, e.g. `America/Los_Angeles` 
before 1883).
   - Values near the `i64` microsecond limits: `localDateTimeToMicros` uses 
exact arithmetic and throws
     `ArithmeticException` on overflow.
   - Literal vs column for each argument, including per-row zone columns.
   - Collated string zone arguments are accepted in 4.x 
(`StringTypeWithCollation(supportsTrimCollation = true)`).
   
   ### Differences across Spark versions
   
   - 3.4 / 3.5: zone inputs are plain `StringType`; an invalid zone surfaces as 
a raw `java.time.DateTimeException`
     (`ZoneRulesException`) rather than `INVALID_TIMEZONE`.
   - 4.0: `INVALID_TIMEZONE` error condition; collated string inputs accepted.
   - 4.1 / 4.2: none found.
   
   ### Implementation notes
   
   - Spark: 
`sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/datetimeExpressions.scala`,
     `ConvertTimezone`; logic is `DateTimeUtils.convertTimestampNtzToAnotherTz`
     
(`microsToLocalDateTime(micros).atZone(src).withZoneSameInstant(tgt).toLocalDateTime`).
   - DataFusion core has no `convert_timezone`. `to_local_time` and Arrow casts 
between `Timestamp(_, Some(tz))` types
     only change metadata or convert to local time for one zone; they do not 
resolve DST gaps/overlaps the Java way.
   - Reuse `SparkTimeZone` in `crates/spark/src/function/datetime/timezone.rs` 
(already used by `from_utc_timestamp` /
     `to_utc_timestamp`) for zone parsing, and follow those functions for 
scalar-vs-array zone handling. The
     local-to-instant step needs a helper with Java's gap/overlap rules 
(`chrono`'s `from_local_datetime` returning
     `LocalResult::None` / `Ambiguous` must be mapped to "shift forward by the 
gap" / "earliest"); `to_utc_timestamp`
     currently looks up the offset with `offset_from_utc_datetime` on the local 
value, which is not the same rule. Note the
     documented `chrono-tz` limitation after 2099. Return 
`Timestamp(Microsecond, None)`.
   - Tests: `crates/sqllogictest/test_files/datetime/convert_timezone.slt` 
exists but every query is commented out
     (ported placeholders). Uncomment and expand it. Document with 
`#[user_doc]`.
   
   ---
   _This issue was generated by an LLM (Claude) from the Apache Spark source 
code and tests. Examples come from Spark's `ExpressionDescription` examples, 
test suites and `.sql.out` golden files, but please verify them against Spark 
before relying on them._


-- 
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]

Reply via email to