[CALCITE-2980] Implement the FORMAT clause of the CAST operator

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.37.0
• Labels: pull-request-available
• Assignee: Jerin John
• Reporter: Julian Hyde
• Created: 04/Apr/19 17:47
• Updated: 6 days ago 16:09
• Resolved: 11/Apr/24 23:32

Description

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:

cast('01-05-2017' as date format 'DD-MM-YYYY')

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

<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

SQL:2016 also introduced the FORMAT clause for CAST which is the standard way to do string <> datetime conversions

<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>

For example:

CAST(<datetime> AS <char string type> [FORMAT <template>])
CAST(<char string> AS <datetime type> [FORMAT <template>])
cast(dt as string format 'DD-MM-YYYY')

Issue Links

• is duplicated by: CALCITE-4346 strange result in toString function with bigdecimal type
• is related to: CALCITE-2919 Implement TO_TIMESTAMP(value, [format]) function
• is related to: IMPALA-4018 Add support for SQL:2016 datetime templates/patterns/masks to CAST(... AS ... FORMAT <template>)
• relates to: CALCITE-5357 Implement FORMAT_TIMESTAMP, FORMAT_DATE, FORMAT_TIME, FORMAT_DATETIME functions (enabled in BigQuery library)
• relates to: CALCITE-6270 Support FORMAT in CAST from Numeric and BYTES to String (Enabled in BigQuery)
• relates to: CALCITE-6367 Add timezone support for FORMAT clause in CAST (enabled in BigQuery)
• relates to: CALCITE-6375 Enable failing quidem tests for FORMAT in CAST
• relates to: CALCITE-6269 Fix missing/broken BigQuery date-time format elements
• relates to: CALCITE-5619 Support PostgreSQL's TO_CHAR function
• links to: GitHub Pull Request #3677