andygrove opened a new issue, #225:
URL: https://github.com/apache/datafusion-spark/issues/225
Part of #175 and apache/datafusion-comet#7300.
### What the function does
`date_format(timestamp, fmt)` formats a timestamp as a `STRING` using a
Spark datetime pattern
(`java.time.DateTimeFormatter`-based, see Spark's "Datetime Patterns" doc).
The first argument is declared
`TIMESTAMP` with `ImplicitCastInputTypes`, so `DATE`, `TIMESTAMP_NTZ` and
`STRING` inputs are implicitly cast to
`TIMESTAMP` (in the session time zone) before formatting. `fmt` is a
`STRING` (literal or column). The result is
`STRING`, NULL if either argument is NULL. The value is rendered in
`spark.sql.session.timeZone`, using `Locale.US`
for text fields. Since 1.5.0. In 4.x, `to_char(datetime, fmt)` /
`to_varchar(datetime, fmt)` produce the same result.
DataFusion core already registers `date_format` as an alias of `to_char`,
which takes chrono `strftime` patterns
(`%Y-%m-%d`). That is incompatible with Spark patterns (`yyyy-MM-dd`), so
this crate must override the name.
### Examples
Session time zone `America/Los_Angeles` (from `ExpressionDescription`,
`DateExpressionsSuite` and
`datetime-formatting.sql.out`):
```sql
SELECT date_format('2016-04-08', 'y');
-- 2016
SELECT date_format(date'2023-08-18', 'yyyy-MM-dd');
-- 2023-08-18
SELECT date_format(timestamp_ltz'2023-08-18 09:13:14.123456Z', 'yyyy-MM-dd
HH:mm:ss.SSSSSSZ');
-- 2023-08-18 02:13:14.123456-0700
SELECT date_format(timestamp_ntz'2023-08-18 09:13:14.123456', 'yyyy-MM-dd
HH:mm:ss.SSSSSS');
-- 2023-08-18 09:13:14.123456
-- col = timestamp '2018-11-17 13:33:33.123Z' (05:33:33.123 local)
SELECT date_format(col, 'y yy yyy yyyy yyyyy yyyyyy'); -- 2018 18 2018
2018 02018 002018
SELECT date_format(col, 'Q QQ QQQ QQQQ'); -- 4 04 Q4 4th
quarter
SELECT date_format(col, 'M MM MMM MMMM'); -- 11 11 Nov
November
SELECT date_format(col, 'E EE EEE EEEE'); -- Sat Sat Sat
Saturday
SELECT date_format(col, 'S SS SSS SSSS SSSSSS SSSSSSSSS'); -- 1 12 123 1230
123000 123000000
SELECT date_format(col, 'h a'); -- 5 AM
SELECT date_format(col, 'z zzzz VV'); -- PST Pacific
Standard Time America/Los_Angeles
SELECT date_format(col, 'X XX XXX Z ZZZZ O OOOO'); -- -08 -0800 -08:00
-0800 GMT-08:00 GMT-8 GMT-08:00
SELECT date_format(col, '[yyyy-MM-dd HH:mm:ss]'); -- 2018-11-17
05:33:33 (optional section is always printed)
-- col = timestamp '1582-06-01 11:33:33.123UTC+080000' (LMT offset of
America/Los_Angeles)
SELECT date_format(col, 'XXX XXXXX'); -- -07:52 -07:52:58
SELECT date_format(col, "''"); -- '
SELECT date_format(col, ''); -- (empty string)
SELECT date_format('X', 'unknown');
-- ANSI on: CAST_INVALID_INPUT ('X' cannot be cast to "TIMESTAMP"); ANSI
off: NULL
SELECT date_format('2018-11-17 13:33:33.333', 'Y');
-- SparkUpgradeException
INCONSISTENT_BEHAVIOR_CROSS_VERSION.DATETIME_PATTERN_RECOGNITION
SELECT date_format('2018-11-17 13:33:33.333', 'e');
-- INCONSISTENT_BEHAVIOR_CROSS_VERSION.DATETIME_WEEK_BASED_PATTERN
SELECT date_format('2018-11-17 13:33:33.333', 'A');
-- INVALID_DATETIME_PATTERN.ILLEGAL_CHARACTER
SELECT date_format('2018-11-17 13:33:33.333', 'qqqqq');
-- INVALID_DATETIME_PATTERN.LENGTH
SELECT date_format('2018-11-17 13:33:33.333', 'V');
-- INVALID_DATETIME_PATTERN.WITH_SUGGESTION
```
### Edge cases to test
- NULL timestamp, NULL format, both NULL. Column (non-literal) format: the
formatter is built per row; literal
formats are compiled once.
- Pattern validation (`DateTimeFormatterHelper.convertIncompatiblePattern`,
applied outside `'...'` quotes):
- week-based letters `Y w W u e c` are rejected. `Y w W u` give
`SparkUpgradeException`
`INCONSISTENT_BEHAVIOR_CROSS_VERSION.DATETIME_PATTERN_RECOGNITION`
because the legacy `SimpleDateFormat` would
accept them. `e c` give `DATETIME_WEEK_BASED_PATTERN`.
- `A B n N p` are `INVALID_DATETIME_PATTERN.ILLEGAL_CHARACTER`.
- 5 letters of `G M L E Q q`, or 7+ `y`, are
`INVALID_DATETIME_PATTERN.LENGTH` (or `DATETIME_PATTERN_RECOGNITION`
when `SimpleDateFormat` accepts the pattern, e.g. `GGGGG`, `MMMMM`,
`EEEEE`, `yyyyyyy`).
- Other patterns `DateTimeFormatter` rejects (`ddd`, `HHH`, `aa`, 10x `S`,
`zzzzz`, `ZZZZZZ`, `FF`) are
`DATETIME_PATTERN_RECOGNITION`. Unknown letters (`C`, `I`, `V`, `OO`,
`XXXXXX`, `xxxxxx`) are
`INVALID_DATETIME_PATTERN.WITH_SUGGESTION`.
These errors fire even when `spark.sql.legacy.timeParserPolicy=CORRECTED`,
because the legacy check always runs
for TIMESTAMP formatters (`validatePatternString(checkLegacy = true)`).
- `y` is rewritten to `u` (proleptic year, signed) unless the pattern
contains `G`. Years > 9999 print with a
leading `+` and negative years with `-` (e.g. the default pattern renders
year -99 as `-0099-01-01 00:00:00`).
With `G`, the year-of-era is printed (`0100-01-01 00:00:00 BC` for the
same instant). `yy` is two-digit.
- Fraction `S` x 1..9: digits beyond microseconds are zero-padded (`SSSSSSS`
gives `1234560`). `SSS` truncates and
does not round.
- Text fields use `Locale.US`: `MMM`/`MMMM`, `E`..`EEEE`, `QQQ`/`QQQQ`, `a`,
`G`..`GGGG` (`Anno Domini`).
- Time zone letters: `VV` is the zone id. `z`/`zzzz` are short and long
names; they depend on DST and on JDK
locale data, and are the hardest to match. `O`, `X`, `x`, `Z` are offset
forms. Offsets with seconds occur for
pre-1900 LMT (`-07:52:58`).
- `F` follows the JDK's semantics (gives 3 for both the 17th and the 31st in
the golden file); verify before
supporting it.
- Literals: `'...'`, `''` for a single quote, non-pattern characters, and
non-ASCII text (`姚123'GyYq...'` is copied
verbatim). Optional sections `[...]` are always printed when formatting.
- Session time zone and DST: the same instant formats differently across
zones. Test instants on both sides of a
transition, and `TIMESTAMP_NTZ`/`DATE` inputs, which are first cast to
`TIMESTAMP` in the session zone (an NTZ
value inside a DST gap is shifted forward by the cast).
- Microsecond precision; timestamps near the `i64` microsecond limits (years
-290308 and +294247).
- String input: valid cast forms (`'2016-04-08'`, `'2018-11-17
13:33:33.333'`). Invalid strings give
`CAST_INVALID_INPUT` with ANSI on and NULL with ANSI off.
- `spark.sql.legacy.timeParserPolicy=LEGACY` switches to `SimpleDateFormat`
(`LegacySimpleTimestampFormatter`),
which has a different letter set and week semantics. Reasonable to report
as unsupported.
### Differences across Spark versions
- `spark.sql.legacy.timeParserPolicy` defaulted to `EXCEPTION` in 3.4/3.5
and `CORRECTED` in 4.0+. Formatting
results are unchanged. Pattern errors classify the same, because the
legacy check is not policy-dependent.
- Error classes: in 3.4/3.5, `ILLEGAL_CHARACTER` / `LENGTH` / `WEEK_BASED` /
`WITH_SUGGESTION` cases were a plain
`java.lang.IllegalArgumentException` (only `DATETIME_PATTERN_RECOGNITION`
had a class). In 4.0 they became
`SparkIllegalArgumentException` with the classes above. `V`/`C`/`I`-style
patterns were still a raw
`IllegalArgumentException` in 4.0 and became
`INVALID_DATETIME_PATTERN.WITH_SUGGESTION` in 4.1.
- ANSI is on by default from 4.0, so invalid string input errors by default.
- 4.0 widened `fmt` to collated strings (`StringTypeWithCollation`). No
behaviour change for UTF8_BINARY.
### Implementation notes
- Spark:
`sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/datetimeExpressions.scala`,
`DateFormatClass` (uses the `TimestampFormatterHelper` trait). Formatting:
`sql/api/src/main/scala/org/apache/spark/sql/catalyst/util/TimestampFormatter.scala`
(`Iso8601TimestampFormatter.format`). Pattern handling:
`DateTimeFormatterHelper.scala` (`convertIncompatiblePattern`,
`buildFormatter`).
- DataFusion core `to_char` (alias `date_format`) uses chrono `strftime`, so
don't wrap it. Write a Spark pattern
compiler: tokenize the pattern (letters, quoted literals, `[ ]`), apply
Spark's validation rules and error classes,
and emit a sequence of chrono-based field printers. Make it a shared
module under
`crates/spark/src/function/datetime/` because `from_unixtime`, `to_char`,
and the parsing functions
(`to_timestamp`, `to_date`, `unix_timestamp`) need the same compiler.
- A verified native subset is realistic: `y`/`yy`/`yyyy` (incl. `+`/`-`
years), `M`..`MMMM`, `d`, `D`, `H`, `h`, `k`,
`K`, `m`, `s`, `S` x 1..9, `a`, `E`..`EEEE`, `Q`..`QQQQ`, `q`, `L`,
`G`..`GGGG`, `X`/`x`/`Z` offsets, `VV`, quoted
literals and `[ ]`. Zone names (`z`, `zzzz`, `O`) and `F` need extra care.
For anything outside the subset, the
kernel should return a clear "unsupported pattern" error so callers
(Comet) can fall back at plan time when `fmt`
is a literal.
- Reuse `SparkTimeZone` in `crates/spark/src/function/datetime/timezone.rs`
to resolve the session zone
(`datafusion.execution.time_zone`) with Spark's zone-id rules. Read ANSI
from
`datafusion.execution.enable_ansi_mode`. This crate's `spark_cast`
(`function/conversion/cast.rs`) only handles
numeric to timestamp. `STRING` input needs a Spark string-to-timestamp
cast (`DateTimeUtils.stringToTimestamp`
semantics); until one exists, accept only
`TIMESTAMP`/`DATE`/`TIMESTAMP_NTZ` natively and let callers cast.
- Tests: `crates/sqllogictest/test_files/datetime/date_format.slt` exists
but every query is commented out (ported
placeholder). Uncomment and expand it with the pattern table from Spark's
`datetime-formatting.sql` and the
invalid patterns from `datetime-formatting-invalid.sql`. 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]