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]

Reply via email to