[
https://issues.apache.org/jira/browse/FLINK-41281?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
ASF GitHub Bot updated FLINK-41281:
-----------------------------------
Labels: pull-request-available (was: )
> Allow CAST from a VARIANT timestamp to the other temporal types
> ---------------------------------------------------------------
>
> Key: FLINK-41281
> URL: https://issues.apache.org/jira/browse/FLINK-41281
> Project: Flink
> Issue Type: Improvement
> Components: Table SQL / API
> Reporter: Ramin Gharib
> Assignee: Ramin Gharib
> Priority: Major
> Labels: pull-request-available
> Fix For: 2.5.0
>
>
> h3. Problem
> A cast from {{VARIANT}} accepts a timestamp only for its own type. A variant
> {{TIMESTAMP_LTZ}} casts to {{TIMESTAMP_LTZ(p)}} and fails for every other
> temporal target. With the session time zone {{Europe/Berlin}} and
> {{v_timestamp_ltz = CAST(TO_TIMESTAMP_LTZ(1704151800123, 3) AS VARIANT)}},
> the instant {{2024-01-01 23:30:00.123Z}}:
> ||Query||Today||Proposed||
> |{{CAST(v_timestamp_ltz AS TIMESTAMP(3))}}|fails with {{Cannot cast a VARIANT
> TIMESTAMP_LTZ value to TIMESTAMP(3). A VARIANT cast does not change the type
> of the stored value, so cast it to its own type first and then convert with a
> regular cast.}}|{{2024-01-02 00:30:00.123}}|
> |{{CAST(v_timestamp_ltz AS DATE)}}|fails with {{VariantTypeException:
> Expected type DATE but got TIMESTAMP_LTZ}}|{{2024-01-02}}|
> |{{CAST(v_timestamp_ltz AS TIME(3))}}|fails with {{Cannot cast a VARIANT
> TIMESTAMP_LTZ value to TIME(3). ...}}|{{00:30:00.123}}|
> Today a user has to know the stored kind and nest two casts, such as
> {{CAST(CAST(v AS TIMESTAMP_LTZ(3)) AS DATE)}}. A column that mixes
> {{TIMESTAMP}} and {{TIMESTAMP_LTZ}} values needs a chain of {{TRY_CAST}}
> calls.
> h3. Proposal
> A stored {{TIMESTAMP}} or {{TIMESTAMP_LTZ}}, including the nanosecond kinds,
> casts to the other temporal types the same way a regular cast from that type
> does, using the session time zone.
> ||Stored kind||Today||Adds||
> |{{TIMESTAMP}}, {{TIMESTAMP_NS}}|{{TIMESTAMP(p)}}|{{TIMESTAMP_LTZ(p)}},
> {{DATE}}, {{TIME(p)}}|
> |{{TIMESTAMP_LTZ}},
> {{TIMESTAMP_LTZ_NS}}|{{TIMESTAMP_LTZ(p)}}|{{TIMESTAMP(p)}}, {{DATE}},
> {{TIME(p)}}|
> |{{DATE}}|{{DATE}}|nothing|
> |{{TIME}}|{{TIME(p)}}|nothing|
> * {{DATE}} and {{TIME}} stay own-type only. Widening them would add something
> the value never had: a time of day, a date, or a zone offset. The regular
> {{TIME}} to {{TIMESTAMP}} cast shows the problem, since it fills in
> 1970-01-01 (FLINK-24423).
> * A {{DATE}} target that gets any other kind fails with the standard VARIANT
> cast message instead of the raw {{VariantTypeException}}.
> * {{VARIANT_GET}} and {{TRY_VARIANT_GET}} follow the same rules.
> * Values are read through {{java.time}}, not the millisecond helpers of the
> regular casts, which are wrong before 1970 (FLINK-41280).
> * The VARIANT section of {{data-types.md}} is updated.
> Trade-off: casts to {{DATE}} and {{TIME}} drop part of the timestamp, while
> the numeric rule rejects {{7.2}} to {{INT}}. Cutting off fractional seconds
> beyond the target precision is already allowed, and Spark, Snowflake and
> Trino apply their regular cast rules here.
> h3. Tests
> * The queries above, for {{TIMESTAMP}} and {{TIMESTAMP_LTZ}} with the micro
> and nanosecond kinds, in two session time zones.
> * A value before 1970 to {{DATE}} and {{TIME}}.
> * Stored {{DATE}} and {{TIME}} still fail for the other targets, and
> {{TRY_CAST}} returns NULL.
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)
