[HIVE-21577] Use SQL:2016 patterns for formatting/parsing dates/timestamps by default

• Type: Improvement
• Status: Resolved
• Priority: Major
• Resolution: Abandoned
• Assignee: Karen Coppage
• Reporter: Karen Coppage
• Created: 04/Apr/19 15:37
• Updated: 19/Jun/19 12:31
• Resolved: 19/Jun/19 12:31

Description

Dates and timestamps objects should be parsed and formatted using the SQL:2016 formats.
1. Formatting
Date/timestamp casts to string should be:

• date: yyyy-mm-dd
• timestamps: yyyy-mm-dd hh24:mi:ss OR yyyy-mm-dd hh24:mi:ss.ff

2. Parsing
Currently accepted timestamp/date formats in Hive 2 and/or Hive 3:
yyyy-mm-dd
yyyy-mm-dd hh24:mi ( ? )
yyyy-mm-dd hh24:mi:ss
yyyy-mm-dd hh24:mi:ss.ff

yyyy-mm-dd hh24:mi[space]<timezone>
yyyy-mm-dd hh24:mi:ss[space]<timezone>
yyyy-mm-dd hh24:mi:ss.ff[space]<timezone>

yyyy-mm-ddThh24:mi
yyyy-mm-ddThh24:mi:ss
yyyy-mm-ddThh24:mi:ss.ff

yyyy-mm-ddThh24:miZ
yyyy-mm-ddThh24:mi:ssZ
yyyy-mm-ddThh24:mi:ss.ffZ

The time zone must be valid ( ? ) but is ignored because date/timestamp are time zone agnostic (see HIVE-20792)
yyyy-mm-ddThh24:mi[space]<timezone>
yyyy-mm-ddThh24:mi:ss[space]<timezone>
yyyy-mm-ddThh24:mi:ss.ff[space]<timezone>

Additional parsing requirements:

• Year 0000 defaults to 0001
• Out-of-range values are accepted and carried over. E.g. "2019-02-30" parses to 2019-03-02.

3. If any new formats are accepted with this change, a large number of tests will need updating. Some examples: TestTimestampParser, package org.apache.hadoop.hive.ql.udf.generic, TestVectorTypeCasts[WithFormat], update_all_types.q, TestNegativeCliDriver with date_literal2.q, vectorized_timestamp_funcs.q

Issue Links

• is a child of: HIVE-21575 Add support for SQL:2016 datetime templates/patterns/masks and CAST(... AS ... FORMAT <pattern>)