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: [email protected]
For additional commands, e-mail: [email protected]
