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]