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]

Reply via email to