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]

Reply via email to