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]