[
https://issues.apache.org/jira/browse/CALCITE-2980?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17819026#comment-17819026
]
Jerin John commented on CALCITE-2980:
-------------------------------------
I have created this [PR|https://github.com/apache/calcite/pull/3677] with the
parser changes for the initial support of {{FORMAT}} with {{CAST}} operator,
along with rex conversion logic using the existing FormatModel class as
suggested by [~tjbanghart] above. (review would be appreciated)
Noticed a few missing cases in the current implementation of {{FormatModels}}
(wrt BQ dialect), wanted to consider breaking them into individual JIRA issues
and PRs so that the new changes can be reviewed independent from
fixes/additions. Created the below JIRA cases to document these cases, we can
discuss the details about how to take them forward; [~tanclary] and I thought
we could start with merging these parser changes from this issue PR followed by
incremental changes for each issue.
[CALCITE-6269|https://issues.apache.org/jira/browse/CALCITE-6269] Fix
missing/broken BigQuery date-time format elements
[CALCITE-6270|https://issues.apache.org/jira/browse/CALCITE-6270] Support
FORMAT in CAST from Numeric and BYTES to String (Enabled in BigQuery)
Also, many of the available quidem tests from
[cast-with-format.iq|https://github.com/apache/calcite/blob/main/core/src/test/resources/sql/cast-with-format.iq]
are failing at this moment; we could manually enable the successful ones, but
it might be better to suppress them until most functionality support is pushed.
cc: [~julianhyde] [~tanclary] [~mbudiu]
> Implement the FORMAT clause of the CAST operator
> ------------------------------------------------
>
> Key: CALCITE-2980
> URL: https://issues.apache.org/jira/browse/CALCITE-2980
> Project: Calcite
> Issue Type: Bug
> Reporter: Julian Hyde
> Assignee: Jerin John
> Priority: Major
> Labels: pull-request-available
>
> SQL:2016 adds an optional {{FORMAT format}} clause to the {{CAST}} operator.
> It is a standard way to do what functions like {{TO_DATE}}, {{TO_NUMBER}},
> {{TO_CHAR}}, {{TO_TIMESTAMP}} have done in an ad hoc way (and with differing
> specifications among databases).
> Here is an example:
> {code:java}
> cast('01-05-2017' as date format 'DD-MM-YYYY')
> {code}
> The following paragraphs are copied from IMPALA-4018, which describes
> implementing this in Impala. (That case also describes cases where the
> implementations of {{TO_TIMESTAMP}} etc. in Hive, Impala, Oracle and
> PostgreSQL are not consistent with each other. We should take note as we
> implement these functions in Calcite.)
> SQL:2016 defines the following datetime templates
> {noformat}
> <datetime template> ::=
> { <datetime template part> }...
> <datetime template part> ::=
> <datetime template field>
> | <datetime template delimiter>
> <datetime template field> ::=
> <datetime template year>
> | <datetime template rounded year>
> | <datetime template month>
> | <datetime template day of month>
> | <datetime template day of year>
> | <datetime template 12-hour>
> | <datetime template 24-hour>
> | <datetime template minute>
> | <datetime template second of minute>
> | <datetime template second of day>
> | <datetime template fraction>
> | <datetime template am/pm>
> | <datetime template time zone hour>
> | <datetime template time zone minute>
> <datetime template delimiter> ::=
> <minus sign>
> | <period>
> | <solidus>
> | <comma>
> | <apostrophe>
> | <semicolon>
> | <colon>
> | <space>
> <datetime template year> ::=
> YYYY | YYY | YY | Y
> <datetime template rounded year> ::=
> RRRR | RR
> <datetime template month> ::=
> MM
> <datetime template day of month> ::=
> DD
> <datetime template day of year> ::=
> DDD
> <datetime template 12-hour> ::=
> HH | HH12
> <datetime template 24-hour> ::=
> HH24
> <datetime template minute> ::=
> MI
> <datetime template second of minute> ::=
> SS
> <datetime template second of day> ::=
> SSSSS
> <datetime template fraction> ::=
> FF1 | FF2 | FF3 | FF4 | FF5 | FF6 | FF7 | FF8 | FF9
> <datetime template am/pm> ::=
> A.M. | P.M.
> <datetime template time zone hour> ::=
> TZH
> <datetime template time zone minute> ::=
> TZM
> {noformat}
> SQL:2016 also introduced the {{FORMAT}} clause for {{CAST}} which is the
> standard way to do string <> datetime conversions
> {noformat}
> <cast specification> ::=
> CAST <left paren>
> <cast operand> AS <cast target>
> [ FORMAT <cast template> ]
> <right paren>
> <cast operand> ::=
> <value expression>
> | <implicitly typed value specification>
> <cast target> ::=
> <domain name>
> | <data type>
> <cast template> ::=
> <character string literal>
> {noformat}
> For example:
> {noformat}
> CAST(<datetime> AS <char string type> [FORMAT <template>])
> CAST(<char string> AS <datetime type> [FORMAT <template>])
> cast(dt as string format 'DD-MM-YYYY')
> {noformat}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
