andygrove opened a new issue, #228:
URL: https://github.com/apache/datafusion-spark/issues/228
Part of #175 and apache/datafusion-comet#7300.
### What the function does
`to_timestamp(timestamp_str[, fmt])` parses a value to a timestamp. The
result type follows
`spark.sql.timestampType`: `TIMESTAMP` by default, `TIMESTAMP_NTZ` when it
is set to `TIMESTAMP_NTZ`.
`try_to_timestamp` is the same, except that parse failures always return
NULL, even in ANSI mode.
`to_timestamp` since 2.2.0, `try_to_timestamp` since 3.4.0.
(`to_timestamp_ltz` / `to_timestamp_ntz` are the same
`ParseToTimestamp` with a fixed result type.)
`ParseToTimestamp` is `RuntimeReplaceable`:
- **Without `fmt`** it becomes `CAST(timestamp_str AS <result type>)` (ANSI
cast for `to_timestamp` when ANSI is on;
never ANSI for `try_`). So the one-argument form needs Spark's
string-to-timestamp cast, not a pattern parser.
- **With `fmt`** it becomes `GetTimestamp(timestamp_str, fmt, <result
type>)`. That parses `STRING` input with the
Spark datetime pattern in the session time zone. `DATE` input becomes
midnight in the session zone; `TIMESTAMP` /
`TIMESTAMP_NTZ` input passes through.
Accepted first-argument types (`ImplicitCastInputTypes`): `STRING`, `DATE`,
`TIMESTAMP`, `TIMESTAMP_NTZ`, plus any
numeric type when the result is `TIMESTAMP`. Numeric input is cast as
seconds since the epoch. `fmt` is a `STRING`.
The result is NULL if either argument is NULL.
### Examples
Session time zone `America/Los_Angeles`. Outputs come from
`timestamp.sql.out`, `datetime-parsing.sql.out`,
`datetime-parsing-invalid.sql.out`, `try_datetime_functions.sql.out` and the
`timestampNTZ/` and `nonansi/`
variants.
```sql
SELECT to_timestamp('2016-12-31 00:12:00'), to_timestamp('2016-12-31',
'yyyy-MM-dd');
-- 2016-12-31 00:12:00 2016-12-31 00:00:00
SELECT to_timestamp(1);
-- 1969-12-31 16:00:01 (with spark.sql.timestampType=TIMESTAMP_NTZ:
NULL)
SELECT to_timestamp('2019-10-06 10:11:12.1234', 'yyyy-MM-dd
HH:mm:ss.SSSSSS[zzz]');
-- 2019-10-06 10:11:12.1234
SELECT to_timestamp('2019-10-06 10:11:12.123UTC', 'yyyy-MM-dd
HH:mm:ss.SSSSSS[zzz]');
-- 2019-10-06 03:11:12.123 (TIMESTAMP_NTZ result: 2019-10-06
10:11:12.123, zone ignored)
SELECT to_timestamp('2019-10-06 10:11:12.12345CST', 'yyyy-MM-dd
HH:mm:ss.SSSSSS[zzz]');
-- 2019-10-06 08:11:12.12345
SELECT to_timestamp('2019-10-06 10:11', 'yyyy-MM-dd HH:mm[:ss.SSSSSS]');
-- 2019-10-06 10:11:00
SELECT to_timestamp('12.12342019-10-06S10:11', "ss.SSSSyyyy-MM-dd'S'HH:mm");
-- 2019-10-06 10:11:12.1234
SELECT to_timestamp("2019-10-06T10:11:12'12", "yyyy-MM-dd'T'HH:mm:ss''SSSS");
-- 2019-10-06 10:11:12.12
SELECT to_timestamp('16', 'dd'), to_timestamp('2019 40', 'yyyy mm');
-- 1970-01-16 00:00:00 2019-01-01 00:40:00
SELECT to_timestamp('99', 'yy'), to_timestamp('2020-365', 'yyyy-DDD'),
to_timestamp('009999', 'y');
-- 2099-01-01 00:00:00 2020-12-30 00:00:00 9999-01-01 00:00:00
SELECT to_timestamp('2019-10-06 10:11:12.1234567PST', 'yyyy-MM-dd
HH:mm:ss.SSSSSS[zzz]');
-- ANSI on: CANNOT_PARSE_TIMESTAMP "Text '2019-10-06 10:11:12.1234567PST'
could not be parsed, unparsed text found at index 26"
-- ANSI off / try_to_timestamp: NULL
SELECT to_timestamp('02-29', 'MM-dd');
-- ANSI on: CANNOT_PARSE_TIMESTAMP "Invalid date 'February 29' as '1970' is
not a leap year"; ANSI off: NULL
SELECT to_timestamp('294248', 'y');
-- java.lang.ArithmeticException: long overflow (ANSI on or off)
SELECT try_to_timestamp('2016-12-31 abc'), try_to_timestamp('foo',
'yyyy-MM-dd');
-- NULL NULL
SELECT try_to_timestamp('22 05 2020 Friday', 'dd MM yyyy EEEEEE');
-- SparkUpgradeException
INCONSISTENT_BEHAVIOR_CROSS_VERSION.DATETIME_PATTERN_RECOGNITION (try_ does not
hide pattern errors)
```
### Edge cases to test
- NULL in each argument. Column formats. Literal formats are compiled once.
- Parsing semantics (shared with `unix_timestamp` / `to_date`):
- case-insensitive, STRICT resolver, and the whole input must be consumed;
- missing fields default to 1970-01-01 00:00:00;
- `D` must be consistent with `M`/`d` (`'2020-30-365'` with `yyyy-dd-DDD`
gives `2020-12-30`, while
`'2020-01-365'` fails);
- `yy` base 2000; `y` accepts 1..n digits, but `yy` needs exactly 2 and
`yyy` at least 3 (`'1'` with `yyy`
fails);
- `y` is rewritten to `u`, so negative years parse unless the pattern has
`G`;
- `S` x n accepts 1..n digits (more fails). Nanoseconds are truncated to
microseconds;
- `[...]` optional sections; `''` and `'text'` literals;
- a zone in the text (`zzz`, `XXX`, `VV`) overrides the session zone for
`TIMESTAMP` results and is ignored for
`TIMESTAMP_NTZ` results.
- Parse-only rejected letters `E F q Q`
(`INVALID_DATETIME_PATTERN.ILLEGAL_CHARACTER`), plus every `date_format`
pattern rule. For `TIMESTAMP` results, patterns `SimpleDateFormat` would
accept raise `SparkUpgradeException`
`DATETIME_PATTERN_RECOGNITION` (e.g. `EEEEE`, `GGGGG`, `yyyyyyy`). Pattern
errors are raised even by
`try_to_timestamp`.
- `TIMESTAMP_NTZ` result: the legacy formatter is never used and the pattern
is validated with
`checkLegacy = false`. From code reading, pattern errors then become
`INVALID_DATETIME_PATTERN.WITH_SUGGESTION`
instead of `SparkUpgradeException`. Also from code reading, a `TIMESTAMP`
input with a format yields its raw UTC
microseconds as NTZ, while without a format the `CAST` uses the session
zone.
- Year range: years up to +/-9999 are normal. 5- and 6-letter `y` fields
accept up to 99999 / 294247; beyond that,
an uncaught `ArithmeticException` is thrown (not affected by ANSI or
`try_`).
- DST: a local time inside a gap is shifted forward by the gap length
(`ZonedDateTime.of`). In an overlap, the
earlier offset is used. Test both in `America/Los_Angeles`.
- ANSI on: `CANNOT_PARSE_TIMESTAMP` (SQLSTATE 22007, `func` =
`try_to_timestamp`). Without a format, the cast
error is `CAST_INVALID_INPUT`. ANSI off and `try_`: NULL.
- `spark.sql.legacy.timeParserPolicy=EXCEPTION` (3.x default): when the new
parser fails and `SimpleDateFormat`
succeeds, Spark throws
`INCONSISTENT_BEHAVIOR_CROSS_VERSION.PARSE_DATETIME_BY_NEW_PARSER`; in 3.5,
`to_timestamp('1', 'yy')` throws it. `LEGACY` uses `SimpleDateFormat`.
Native support can target `CORRECTED`.
- Numeric input (`TIMESTAMP` result only): `INT`, `BIGINT`, `DECIMAL` and
`DOUBLE` seconds, including fractions,
NaN/Infinity and overflow, follow the numeric-to-timestamp `CAST`.
### Differences across Spark versions
- `spark.sql.legacy.timeParserPolicy` default: `EXCEPTION` (3.4, 3.5),
`CORRECTED` (4.0+).
- ANSI is on by default in 4.0+, so `to_timestamp` raises on bad input by
default; `try_to_timestamp` is unaffected.
- The `CANNOT_PARSE_TIMESTAMP` message changed in 4.0. In 3.x it ended `If
necessary set "spark.sql.ansi.enabled"
to "false" to bypass this error.`; from 4.0 it names the `func`
(`try_to_timestamp`) to use instead.
- Pattern-error classes were tightened in 4.0 and 4.1 (see the `date_format`
issue).
- 4.0 widened the string arguments to collated strings.
### Implementation notes
- Spark:
`sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/datetimeExpressions.scala`,
`ParseToTimestamp`, `TryToTimestampExpressionBuilder`, `GetTimestamp`,
`ToTimestamp`. Parser:
`sql/api/src/main/scala/org/apache/spark/sql/catalyst/util/TimestampFormatter.scala`
(`Iso8601TimestampFormatter.parse` / `parseWithoutTimeZone`) and
`DateTimeFormatterHelper.scala`. The one-argument
form uses `Cast` with `SparkDateTimeUtils.stringToTimestamp`.
- DataFusion core `to_timestamp` takes chrono `strftime` formats, tries
several in turn, returns
`Timestamp(Nanosecond)` and errors on failure. Neither the formats nor the
result type match, so don't wrap it.
- Build on the Spark pattern compiler proposed for `date_format`, adding a
parse mode: case-insensitive matching,
variable-length `S`, field defaulting and consistency checks, zone
override, java.time gap/overlap resolution. 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`/`VV`, literals, `[ ]`). Return an "unsupported pattern" error
for zone names (`z`) and anything else
not verified, so callers can fall back.
- The one-argument form needs a Spark string-to-timestamp /
-to-timestamp_ntz cast. This crate has none (its
`spark_cast` handles numeric input only). Numeric input can reuse
`spark_cast`.
- Result type: the kernel needs to know `spark.sql.timestampType`. Either
take it from a config option or, simpler,
expose `to_timestamp_ltz` / `to_timestamp_ntz`-style fixed-type kernels
and let the caller pick one.
- Tests: `to_timestamp.slt` and `try_to_timestamp.slt` exist under
`crates/sqllogictest/test_files/datetime/`, but
every query in them is commented out (ported placeholders). Uncomment them
and port the
`timestamp.sql` / `datetime-parsing*.sql` cases. 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]