andygrove opened a new issue, #211:
URL: https://github.com/apache/datafusion-spark/issues/211
Part of #178 and apache/datafusion-comet#7300.
### What the function does
`to_time(str[, format])` parses a STRING into a TIME value. Both arguments
must be STRING (any collation,
including trim collations); there is no implicit cast from other types, so
`try_to_time(1)` fails analysis
with `DATATYPE_MISMATCH.UNEXPECTED_INPUT_TYPE`. The result type is always
`TIME(6)` (microsecond precision).
NULL `str` or NULL `format` gives NULL.
- Without `format`, the string is parsed with Spark's string-to-TIME cast
rules
(`SparkDateTimeUtils.stringToTime`): leading/trailing whitespace is
trimmed, an optional `T` prefix is
allowed, `HH:mm` and `HH:mm:ss[.ffffff]` with 1- or 2-digit fields,
fractions beyond 6 digits are
truncated, and an optional `AM`/`PM` suffix (case-insensitive) switches to
12-hour parsing (hour 1-12).
Time zones or date parts make the input invalid.
- With `format`, it uses a Java `DateTimeFormatter` built from Spark's
datetime pattern
(`TimeFormatter` / `Iso8601TimeFormatter`). Missing fields default to 0
(`'11'` with `'HH'` is
`11:00:00`). `format` can be a column.
`to_time` raises `CANNOT_PARSE_TIME` (SQLSTATE `22010`) on unparseable
input, regardless of
`spark.sql.ansi.enabled`: "The input string <input> cannot be parsed to a
TIME value because it does not
match to the datetime format <format>." When no format is given, `<format>`
is reported as
`'HH:mm:ss.SSSSSS'`. `try_to_time(str[, format])` is `TryEval(ToTime(...))`,
so it returns NULL instead of
any error. Both added in Spark 4.1.0 and require
`spark.sql.timeType.enabled` (internal, default `false`
outside tests); otherwise analysis fails with `UNSUPPORTED_TIME_TYPE`.
### Examples
```sql
SELECT to_time('00:12:00');
-- 00:12:00
SELECT to_time('12.10.05', 'HH.mm.ss');
-- 12:10:05
SELECT to_time(null), to_time('01:02:03'), to_time('23-59-59.999999',
'HH-mm-ss.SSSSSS');
-- NULL 01:02:03 23:59:59.999999
SELECT to_time('11', 'HH');
-- 11:00:00
SELECT to_time('13-60', 'HH-mm');
-- error CANNOT_PARSE_TIME, input '13-60', format 'HH-mm'
SELECT try_to_time('00:12:00.001');
-- 00:12:00.001
SELECT try_to_time('12.10.05.999999', 'HH.mm.ss.SSSSSS');
-- 12:10:05.999999
SELECT try_to_time('foo', 'HH:mm:ss');
-- NULL
SELECT try_to_time('12:48:31 abc');
-- NULL
SELECT try_to_time('10:11:12.', 'HH:mm:ss.SSSSSS');
-- NULL
SELECT try_to_time('02-69', 'HH-mm');
-- NULL
```
### Edge cases to test
- NULL `str`; NULL `format` as a literal and as a non-foldable column (both
NULL).
- Default parsing (from `DateTimeUtilsSuite` "stringToTime"): `'00:00'` ->
`00:00:00`; `'1:2:3.0'` ->
`01:02:03`; `'T1:02:3.04'` -> `01:02:03.04`; `' 00:00 '` -> `00:00:00`;
`'23:59:59.999999'` kept;
`'24:00:00'`, `'23:60:00'`, `'23:00:60'`, `'-1:00:00'` invalid.
- AM/PM in the default path: `'12:00:00 AM'` -> `00:00:00`, `'12:30:45 PM'`
-> `12:30:45`,
`'5:30:15.123456 AM'` -> `05:30:15.123456`; hour 0 or 13+ with a suffix is
invalid.
- Invalid default-path input: `to_time('100:50')` raises `CANNOT_PARSE_TIME`
with
`format = 'HH:mm:ss.SSSSSS'`.
- Pattern path (`TimeExpressionsSuite` "ParseToTime"): `('23-59-00.000999',
'HH-mm-ss.SSSSSS')` ->
`23:59:00.000999`; `('12.00.59.90909', 'HH.mm.ss.SSSSS')` ->
`12:00:59.90909`;
`(' 12:00.909 ', ' HH:mm.SSS ')` -> `12:00:00.909`; quoted literals
`('12 hours 123 millis', "HH 'hours' SSS 'millis'")` -> `12:00:00.123`;
`('100:50', 'mm:HH')` raises `CANNOT_PARSE_TIME`.
- Literal vs column for both `str` and `format` (Spark caches the formatter
for a foldable format, and
builds one per row otherwise).
- Invalid pattern strings: with `to_time` an error; with `try_to_time` NULL.
- Wrong arity: `try_to_time('11:12:13', 'HH:mm:ss', 'SSSSSS')` raises
`WRONG_NUM_ARGS.WITHOUT_SUGGESTION`.
- Errors do not depend on ANSI mode or session time zone.
### Differences across Spark versions
Not present before 4.1.0. `ToTime`, `TryToTimeExpressionBuilder`,
`TimeFormatter` and `stringToTime`
(including AM/PM support) are identical in 4.1.3 and 4.2.0, and the golden
results match.
### Implementation notes
- Spark:
`sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/timeExpressions.scala`
(`ToTime`, `ToTimeParser`, `TryToTimeExpressionBuilder`);
`sql/api/src/main/scala/org/apache/spark/sql/catalyst/util/TimeFormatter.scala`;
`SparkDateTimeUtils.stringToTime` in
`sql/api/src/main/scala/org/apache/spark/sql/catalyst/util/SparkDateTimeUtils.scala`.
Error builder:
`QueryExecutionErrors.timeParseError`.
- Spark's Arrow type for TIME is `Time64(Nanosecond)` (`ArrowUtils`), values
being nanoseconds since
midnight; return that, holding microsecond-precision values.
- DataFusion core has `to_time`
(`datafusion/functions/src/datetime/to_time.rs`) but it differs: it takes
chrono `strftime` formats, not Spark/Java patterns; it is variadic and
tries formats in order; it skips
NULL formats instead of returning NULL; it requires the format to be a
scalar; its default formats are
only `%H:%M:%S%.f`, `%H:%M:%S`, `%H:%M` (no trimming, `T` prefix or
AM/PM); it keeps nanoseconds instead
of truncating to microseconds; and its error text is not
`CANNOT_PARSE_TIME`. It also has no `try_`
form. Wrapping it is not enough; a Spark pattern translator (or a
dedicated parser) is needed.
- datafusion-spark has no Spark datetime pattern translator yet; one written
here should be shareable with
future `to_timestamp`/`to_date`/`date_format` work. The default (no
format) path is the same logic as
`CAST(str AS TIME)`, so it should live in a helper reusable by the Spark
cast. Put the functions in the
`datetime` module.
- Tests: add `crates/sqllogictest/test_files/datetime/to_time.slt` and
`try_to_time.slt` using the cases
above (from `sql/core/src/test/resources/sql-tests/results/time.sql.out`,
`TimeExpressionsSuite` and
`DateTimeUtilsSuite`), and 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]