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

   Part of #175 and apache/datafusion-comet#7300.
   
   ### What the function does
   
   `unix_timestamp([timeExp[, fmt]])` and `to_unix_timestamp(timeExp[, fmt])` 
return the number of whole seconds since
   `1970-01-01 00:00:00 UTC` as `BIGINT`. Both are subclasses of `ToTimestamp` 
with `downScaleFactor = 1_000_000`,
   and they behave identically except for arity: `unix_timestamp()` with no 
arguments means
   `unix_timestamp(current_timestamp())` (non-deterministic per query), while 
`to_unix_timestamp` needs at least one
   argument. `fmt` defaults to `yyyy-MM-dd HH:mm:ss`.
   
   `timeExp` may be `STRING`, `DATE`, `TIMESTAMP` or `TIMESTAMP_NTZ`. These 
functions use `ExpectsInputTypes`, so
   there is no implicit cast and numeric input is an analysis error. `fmt` is a 
`STRING`.
   - `STRING`: parsed with the Spark datetime pattern `fmt` in the session time 
zone. A zone or offset in the text
     overrides the session zone. The result is the parsed microseconds divided 
by 1_000_000.
   - `DATE`: midnight in the session time zone.
   - `TIMESTAMP` / `TIMESTAMP_NTZ`: the stored microseconds divided by 
1_000_000. For NTZ this means the wall clock is
     read as if it were UTC. `fmt` is ignored, even when it is NULL.
   
   Nullability: with ANSI off the result is always nullable (a parse failure 
gives NULL). With ANSI on it is nullable
   only if an input is. `unix_timestamp` since 1.5.0, `to_unix_timestamp` since 
1.6.0.
   
   ### Examples
   
   Session time zone `America/Los_Angeles`. Outputs come from 
`DateExpressionsSuite`, `datetime-parsing-invalid.sql.out`
   and `timestamp.sql.out`, or from reading the code where noted.
   
   ```sql
   SELECT to_unix_timestamp('2016-04-08', 'yyyy-MM-dd');
   -- 1460098800
   SELECT unix_timestamp('1969-12-31 16:00:00'), unix_timestamp('1969-12-31 
16:16:40');
   -- 0 1000
   SELECT unix_timestamp('1969-12-31 15:43:20.000', 'yyyy-MM-dd HH:mm:ss.SSS');
   -- -1000
   SELECT unix_timestamp(date'2015-07-24', NULL);   -- format ignored for 
non-string input
   -- 1437721200
   SELECT unix_timestamp(CAST(NULL AS DATE), 'yyyy-MM-dd HH:mm:ss');
   -- NULL
   -- truncation toward zero, not floor (session zone UTC):
   SELECT unix_timestamp(timestamp'1969-12-31 23:59:58.5');
   -- -1   (a CAST(... AS BIGINT) gives -2)
   -- DST gap / overlap (derived: java.time shifts a gap forward, picks the 
earlier offset in an overlap)
   SELECT unix_timestamp('2019-03-10 02:30:00'), unix_timestamp('2019-03-10 
03:30:00');
   -- 1552213800        1552213800
   SELECT unix_timestamp('2019-11-03 01:30:00');
   -- 1572769800
   SELECT unix_timestamp('Unparseable', 'yyyy-MM-dd HH:mm:ss.SSS');
   -- ANSI off: NULL
   -- ANSI on : CANNOT_PARSE_TIMESTAMP  message "Text 'Unparseable' could not 
be parsed at index 0", func `try_to_timestamp`
   SELECT unix_timestamp('2020-01-27T20:06:11.847', 'yyyy-MM-dd HH:mm:ss.SSS');
   -- ANSI off: NULL; ANSI on: CANNOT_PARSE_TIMESTAMP
   SELECT unix_timestamp('22 05 2020 Friday', 'dd MM yyyy EEEEE');
   -- SparkUpgradeException 
INCONSISTENT_BEHAVIOR_CROSS_VERSION.DATETIME_PATTERN_RECOGNITION
   SELECT unix_timestamp('2018-01-01', 'aa');
   -- SparkUpgradeException 
INCONSISTENT_BEHAVIOR_CROSS_VERSION.DATETIME_PATTERN_RECOGNITION
   ```
   
   ### Edge cases to test
   
   - NULL `timeExp`; NULL `fmt` with string input (NULL) versus with 
date/timestamp input (format ignored, value
     returned); column formats.
   - Parsing rules (`Iso8601TimestampFormatter.parse`, 
`DateTimeFormatterHelper`):
     - parsing is case-insensitive and STRICT, and the whole string must be 
consumed (`unparsed text found`);
     - missing fields default to year 1970, month 1, day 1, time 00:00:00. 
`'02-29'` with `MM-dd` fails because 1970
       is not a leap year;
     - `a` without an hour field gives 12:00 for PM; `hh` without `a` is AM;
     - `y` is rewritten to `u`, so negative years parse (`-1234-02-22 
02:22:22`). With `G`, a year-of-era is required
       (`BC 1234` is proleptic -1233);
     - `yy` uses base year 2000 (`99` gives 2099);
     - `D` day-of-year must agree with any `M`/`d` given;
     - a run of `S` accepts 1..n fraction digits. More than n digits fails. 
Digits past microseconds are truncated;
     - optional sections `[...]` and quoted literals.
   - Letters rejected only when parsing: `E F q Q` give 
`INVALID_DATETIME_PATTERN.ILLEGAL_CHARACTER`. Week-based
     letters, `A B n N p`, and the length rules are as for `date_format`.
   - Year range: a parsed year whose microseconds overflow `i64` throws an 
uncaught `java.lang.ArithmeticException:
     long overflow` (`'294248'` with `y`), even with ANSI off.
   - Division truncates toward zero for pre-epoch values with a fraction (see 
the example above). This applies to
     both the string and the timestamp paths.
   - Session time zone: string and `DATE` inputs depend on it; `TIMESTAMP` and 
`TIMESTAMP_NTZ` do not. DST gaps (shift
     forward) and overlaps (earlier offset). A zone in the text (`XXX`, `z`, 
`VV`) overrides the session zone.
   - ANSI: `CANNOT_PARSE_TIMESTAMP` (SQLSTATE 22007) with `func` = 
`try_to_timestamp` and the
     `DateTimeParseException` message.
   - `spark.sql.legacy.timeParserPolicy`:
     - `EXCEPTION` (the 3.x default): when the new parser fails but 
`SimpleDateFormat` would succeed, Spark throws
       `SparkUpgradeException` 
`INCONSISTENT_BEHAVIOR_CROSS_VERSION.PARSE_DATETIME_BY_NEW_PARSER`;
     - `CORRECTED`: the failure is NULL, or an error under ANSI;
     - `LEGACY`: Spark uses `SimpleDateFormat`.
     Matching `EXCEPTION` exactly needs a `SimpleDateFormat` emulation, so a 
native kernel should support `CORRECTED`
     and leave the other policies to the caller.
   - Non-determinism of zero-argument `unix_timestamp()`.
   
   ### Differences across Spark versions
   
   - `spark.sql.legacy.timeParserPolicy` default: `EXCEPTION` in 3.4/3.5, 
`CORRECTED` in 4.0+. So in 3.x with ANSI
     off, `unix_timestamp('1', 'yy')`-style inputs raise 
`PARSE_DATETIME_BY_NEW_PARSER` instead of returning NULL.
   - ANSI is on by default from 4.0, so parse failures error by default.
   - The `CANNOT_PARSE_TIMESTAMP` message in 3.4/3.5 was `"<message>. If 
necessary set <ansiConfig> to "false" to
     bypass this error."`. From 4.0 it is `"<message>. Use <func> to tolerate 
invalid input string and return NULL
     instead."`.
   - 4.0 widened the string arguments to collated strings. Pattern-error 
classes changed as described in the
     `date_format` issue.
   
   ### Implementation notes
   
   - Spark: 
`sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/datetimeExpressions.scala`,
     `UnixTimestamp`, `ToUnixTimestamp`, `UnixTime`, `ToTimestamp` (`eval`). 
Parser:
     
`sql/api/src/main/scala/org/apache/spark/sql/catalyst/util/TimestampFormatter.scala`
     (`Iso8601TimestampFormatter.parse`, `extractMicros`) and 
`DateTimeFormatterHelper.scala` (`toLocalDate`,
     `toLocalTime`, `createBuilderWithVarLengthSecondFraction`).
   - DataFusion core `to_unixtime` takes chrono `strftime` formats and tries 
several of them in turn. It does not
     ignore the format for non-strings, uses different defaults and error 
behaviour, and has no ANSI switch. Don't wrap
     it.
   - This crate already has `unix_seconds` 
(`crates/spark/src/function/datetime/unix.rs`). It mirrors Spark's
     `UnixSeconds`, which uses `Math.floorDiv`, so it is not reusable here: 
`unix_timestamp` truncates toward zero.
   - Share the Spark pattern compiler/parser with `to_timestamp` / `to_date` / 
`date_format`. A native path can cover
     a verified subset of literal patterns (numeric `y M d D H h k K m s S`, 
`a`, `MMM`/`MMMM`, `X`/`x`/`Z` offsets,
     quoted literals, `[ ]`) and should return an "unsupported pattern" error 
otherwise, so callers can fall back.
     `DATE` / `TIMESTAMP` / `TIMESTAMP_NTZ` inputs are trivial and can be 
native immediately.
   - Register `to_unix_timestamp` as a separate name (not an alias) because of 
the arity difference. Use
     `Volatility::Stable` for the zero-argument form.
   - Tests: `unix_timestamp.slt` and `to_unix_timestamp.slt` exist under 
`crates/sqllogictest/test_files/datetime/`,
     but every query in them is commented out (ported placeholders). Uncomment 
and expand them. 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