[HIVE-21575] Add support for SQL:2016 datetime templates/patterns/masks and CAST(... AS ... FORMAT <pattern>)

• Type: Improvement
• Status: Resolved
• Priority: Major
• Resolution: Done
• Labels: SQL datetime
• Assignee: Karen Coppage
• Reporter: Karen Coppage
• Created: 04/Apr/19 15:20
• Updated: 09/Sep/24 10:24
• Resolved: 26/Aug/19 10:57

Description

Summary
Timestamp and date handling and formatting is currently implemented in Hive using (sometimes very specific) Java SimpleDateFormat patterns , however, it is not what most standard SQL systems use. For example see Vertica, Netezza, Oracle, and PostgreSQL.

Cast...Format

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

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

Stuff like this wouldn't need to happen.

New SQL:2016 Patterns

Some conflicting examples:

SimpleDateTime: 'MMM dd, yyyy HH:mm:ss'
SQL:2016: 'mon dd, yyyy hh24:mi:ss'

SimpleDateTime: 'yyyy-MM-dd HH:mm:ss'
SQL:2016: 'yyyy-mm-dd hh24:mi:ss'

For the full list of patterns, see subsection "Proposal for Impala’s datetime patterns" in this doc: https://docs.google.com/document/d/1V7k6-lrPGW7_uhqM-FhKl3QsxwCRy69v2KIxPsGjc1k/edit

Continued usage of SimpleDateFormat patterns

[Update] This feature will NOT be behind a flag in order to keep things simple for users. Existing Hive functions that accept SimpleDateFormat patterns as input will continue to do so. Please let me know if you disagree with this decision. These are the functions (afaik) affected:

• from_unixtime(bigint unixtime[, string format])
• unix_timestamp(string date, string pattern)
• to_unix_timestamp(date[, pattern])
• add_months(string start_date, int num_months, output_date_format)
• date_format(date/timestamp/string ts, string fmt)

This description is a heavily edited description of IMPALA-4018.

Issue Links

• is a parent of: HIVE-21577 Use SQL:2016 patterns for formatting/parsing dates/timestamps by default
• is a parent of: HIVE-21576 Introduce CAST...FORMAT and limited list of SQL:2016 datetime formats
• is a parent of: HIVE-21578 Introduce SQL:2016 formats FM, FX, and nested strings
• is a parent of: HIVE-21579 Introduce more complex SQL:2016 datetime formats
• is a parent of: HIVE-21580 Introduce ISO 8601 week numbering SQL:2016 formats
• is a parent of: HIVE-21868 Vectorize CAST...FORMAT
• is a parent of: HIVE-28425 Document CAST FORMAT function in the wiki
• is related to: HIVE-28483 CAST string to date should return null when format is invalid
• is related to: SPARK-27881 Support CAST (... FORMAT <pattern>) expression