Uploaded image for project: 'IMPALA'
  1. IMPALA
  2. IMPALA-4018

Add support for SQL:2016 datetime templates/patterns/masks to CAST(... AS ... FORMAT <template>)

    XMLWordPrintableJSON

    Details

      Description

      Summary
      The format masks/templates for currently are implemented using the Java SimpleDateFormat patterns, and although this is what Hive has implemented, it is not what most standard SQL systems implement. For example see Vertica, Netezza, Oracle, and PostgreSQL.

      Examples of incompatibilities

      -- PostgreSQL/Netezza/Vertica/Oracle
      select to_timestamp('May 15, 2015 12:00:00', 'mon dd, yyyy hh:mi:ss');
      -- Impala
      select to_timestamp('May 15, 2015 12:00:00', 'MMM dd, yyyy HH:mm:ss');
      
      -- PostgreSQL/Netezza/Vertica/Oracle
      select to_timestamp('2015-02-14 20:19:07','yyyy-mm-dd hh24:mi:ss');
      -- Impala
      select to_timestamp('2015-02-14 20:19:07','yyyy-MM-dd HH:mm:ss');
      
      -- Vertica/Oracle
      select to_timestamp('2015-02-14 20:19:07.123456','yyyy-mm-dd hh24:mi:ss.ff');
      -- Impala
      select to_timestamp('2015-02-14 20:19:07.123456','yyyy-MM-dd HH:mm:ss.SSSSSS');
      

      Considerations
      Because this is a change in default behavior for to_timestamp(), if possible, having a feature flag to revert to the legacy Java SimpleDateFormat patterns should be strongly considered. This would allow users to chose the behavior they desire and scope it to a session if need be.

      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')
      cast('01-05-2017' as date format 'DD-MM-YYYY')
      

      Update
      Here is the proposal for the new datetime patterns and their semantics:
      https://docs.google.com/document/d/1V7k6-lrPGW7_uhqM-FhKl3QsxwCRy69v2KIxPsGjc1k/

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                gaborkaszab Gabor Kaszab
                Reporter:
                grahn Greg Rahn
              • Votes:
                2 Vote for this issue
                Watchers:
                7 Start watching this issue

                Dates

                • Created:
                  Updated: