[
https://issues.apache.org/jira/browse/CALCITE-6055?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17777438#comment-17777438
]
Will Noble commented on CALCITE-6055:
-------------------------------------
My bad. Added more context to the description. Let me know if anything is still
unclear or if you think the title could still use tweaking.
> Customize handling of DateTimeTypeName based on type system
> -----------------------------------------------------------
>
> Key: CALCITE-6055
> URL: https://issues.apache.org/jira/browse/CALCITE-6055
> Project: Calcite
> Issue Type: Bug
> Reporter: Will Noble
> Priority: Minor
> Labels: pull-request-available
>
> *Background*:
> In ISO SQL, a {{TIMESTAMP}} comprises the parameters year, month, day, hour,
> minute, and second, henceforth referred to as clock-calendar parameters. This
> does not define an unambiguous instant in time, since timestamps in different
> time zones can have different parameters at the same instant. It is analogous
> to a BigQuery {{DATETIME}}. BigQuery chose to give its native {{TIMESTAMP}}
> type a different definition: “an absolute point in time, independent of any
> time zone or convention such as Daylight Savings Time.”
> ISO SQL has 2 other {{TIMESTAMP}} types. The {{TIMESTAMP WITH TIME ZONE}}
> simply attaches a time zone to the existing {{TIMESTAMP}} type, making it
> _both_ a set of clock-calendar parameters _and_ an unambiguous instant in
> time. The final type -- {{TIMESTAMP WITH LOCAL TIME ZONE}} -- is more subtle.
> According to Oracle, it “is normalized to the database time zone, and the
> time zone offset is not stored as part of the column data. When users
> retrieve the data, Oracle returns it in the users' local session time zone.”
> This act of conversion between the local time zone and the database time zone
> means that users interacting with this kind of timestamp will observe the
> same instant in time even though they see different clock-calendar
> parameters. However, the local time zone is not considered “part of the
> data”. Thus, it has the semantics of an un-zoned, absolute point in time,
> analogous to a BigQuery {{TIMESTAMP}}.
> This creates the confusing situation where a BigQuery {{DATETIME}} is
> actually an ISO {{TIMESTAMP}}, and a BigQuery {{TIMESTAMP}} is actually an
> ISO {{TIMESTAMP WITH LOCAL TIME ZONE}}.
> *Problem*:
> This is very similar to CALCITE-5424, which only applied to literals (e.g.
> {{TIMESTAMP '2023-10-17 12:00:00'}}). We need a similar solution for [the
> {{DateTimeTypeName}}
> construction|https://github.com/apache/calcite/blob/6f79436c178beec639e559d9152c237bbf8ec3e8/core/src/main/codegen/templates/Parser.jj#L6019]
> which is used in other contexts (e.g. {{CAST('2023-10-17 12:00:00' AS
> TIMESTAMP)}}). Seems like we can use a very similar approach: introduce a
> {{DATETIME}} type name, and use {{SqlUserDefinedTypeNameSpec}} for both that
> and the {{TIMESTAMP}} type names. During validation and sql-to-rel
> conversion, we can look up the type names in the UDT map. If no mapping is
> found for {{DATETIME}} (which would occur for any dialect besides BigQuery),
> an error would occur during validation, because that type does not exist in
> that dialect. If no mapping is found for {{TIMESTAMP}}, we would fall back on
> the canonical {{TIMESTAMP}} type, preserving the existing behavior for all
> other dialects.
> *In summary*:
> {{CAST('2023-10-17 12:00:00' AS TIMESTAMP)}} should produce a {{TIMESTAMP
> WITH LOCAL TIME ZONE}} for BigQuery, but a regular {{TIMESTAMP}} for all
> other dialects.
> {{CAST('2023-10-17 12:00:00' AS DATETIME)}} should produce a {{TIMESTAMP}}
> for BigQuery, but throw a validation error for all other dialects.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
