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

• Type: New Feature
• Status: Closed
• Priority: Critical
• Resolution: Fixed
• Affects Version/s: Impala 2.2.4
• Component/s: Frontend
• Labels: ansi-sql compatibility sql-language
• Epic Link: Timestamp Compatibility
• Target Version: Impala 3.4.0
• Assignee: Gabor Kaszab
• Reporter: Greg Rahn
• Created: 25/Aug/16 03:14
• Updated: 09/Jan/20 14:36
• Resolved: 09/Jan/20 14:36

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/

Issue Links

• Blocked: IMPALA-8161 Impala Doc: Document the supported Date and Time patterns
• blocks: IMPALA-5766 Add date/timestamp format options to external tables
• contains: IMPALA-9131 Use single quotes when printing out FORMAT clause within CAST.
• contains: IMPALA-9217 SQL:2016 datetime patterns: Adjust TZH and TZM limits
• duplicates: IMPALA-420 Impala could support MySQL's date_format() function.
• is duplicated by: IMPALA-3381 Impala to support AM/PM format in unix_timestamp and from_unixtime
• is related to: IMPALA-8367 from_unixtime Bad date/time conversion format: u on NULL value
• is related to: IMPALA-3759 Add function for unix_timestamp with date format yyyyDDD
• is related to: SPARK-27881 Support CAST (... FORMAT <pattern>) expression
• is related to: IMPALA-1734 cast(<string> as timestamp) should not require zero padded elements
• relates to: CALCITE-2980 Implement the FORMAT clause of the CAST operator
• relates to: IMPALA-3381 Impala to support AM/PM format in unix_timestamp and from_unixtime
• relates to: IMPALA-5279 More Complete ISO 8601 Date Support
• relates to: IMPALA-1505 Add functions to convert custom date strings with full precision
• relates to: HIVE-21576 Introduce CAST...FORMAT and limited list of SQL:2016 datetime formats
• supercedes: IMPALA-1698 Impala should have function like FORMAT_DATE(timestamp, format)

Sub-Tasks

• Impala Doc: Doc CAST(... AS ... FORMAT <template>): Closed
• SQL:2016 datetime patterns - Milestone 1: Resolved
• SQL:2016 datetime patterns - Milestone 2: Resolved
• SQL:2016 datetime patterns - Milestone 3: Closed
• SQL:2016 datetime patterns - Milestone 4: Resolved
• Impala Doc: Document SQL:2016 datetime patterns - Milestone 2: Closed
• Impala Doc: Document SQL:2016 datetime patterns - Milestone 3: Closed