[jira] [Created] (SPARK-57618) Support casting between TIME(p) and TIMESTAMP_NTZ(q)

["Max Gekk (Jira)" <ji...@apache.org>]

Max Gekk created SPARK-57618:
--------------------------------

             Summary: Support casting between TIME(p) and TIMESTAMP_NTZ(q)
                 Key: SPARK-57618
                 URL: https://issues.apache.org/jira/browse/SPARK-57618
             Project: Spark
          Issue Type: Sub-task
          Components: SQL
    Affects Versions: 4.3.0
            Reporter: Max Gekk
            Assignee: Max Gekk


h2. Summary

Add bidirectional ANSI casts between the {{TIME(p)}} data type (p in [0, 9]) and
{{TIMESTAMP_NTZ(q)}} (q in [6, 9]), where q=6 is the microsecond {{TimestampNTZType}}
and q in [7, 9] is the nanosecond {{TimestampNTZNanosType}}.

h2. Motivation

Spark already supports {{TIME}} casts to/from {{STRING}}, integral, and {{DECIMAL}}
types, but there is no way to convert between {{TIME}} and {{TIMESTAMP_NTZ}}. These
conversions are required by the SQL standard and are a common user need (e.g. attaching
a time-of-day to a timestamp, or extracting the time-of-day from a timestamp).

h2. Semantics (SQL standard)

* {{CAST(TIMESTAMP_NTZ(q) AS TIME(p))}} (standard rule 15.d): take the hour/minute/second
and fractional fields of the source, truncated to precision {{p}}. Deterministic and
time-zone independent.
* {{CAST(TIME(p) AS TIMESTAMP_NTZ(q))}} (standard rule 17.c): the date fields come from
{{CURRENT_DATE}} and the time fields come from the value. As {{CURRENT_DATE}} is stable
within a query, the cast is stabilized via the existing {{ComputeCurrentTime}} rule so
all references in a query observe the same date (matching {{current_date()}}).

Fractional precision handling is pure truncation (no rounding). Both directions always
succeed, so there is no new nullability or error condition.

h2. Examples

{code:sql}
-- extract time-of-day
SELECT CAST(TIMESTAMP_NTZ'2020-05-17 12:34:56.789012' AS TIME(6));
-- 12:34:56.789012

-- attach the current date
SELECT CAST(TIME'12:34:56.789012345' AS TIMESTAMP_NTZ(9));
-- <current_date> 12:34:56.789012345
{code}

h2. Scope

* Allow the four cast pairs in both ANSI and non-ANSI modes (and TRY): TIME <-> the
NTZ family only; TIMESTAMP_LTZ is intentionally not a valid counterpart.
* Interpreted and codegen paths.
* {{TIME -> TIMESTAMP_NTZ}} is stabilized within a query via {{ComputeCurrentTime}}.

h2. Out of scope

* Structured Streaming batch-timestamp parity for {{TIME -> TIMESTAMP_NTZ}}: the cast
uses the optimizer-instant current date rather than the micro-batch timestamp.



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

---------------------------------------------------------------------
To unsubscribe, e-mail: issues-unsubscribe@spark.apache.org
For additional commands, e-mail: issues-help@spark.apache.org