[SPARK-27882] Support SQL:2016 compatible datetime patterns

• Type: Improvement
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 3.1.0
• Component/s: SQL
• Assignee: Unassigned
• Reporter: Peter Toth
• Created: 30/May/19 13:58
• Updated: 16/Mar/20 22:52

Description

Date and time related functions in Spark use DateTimeFormatter to parse and format dates where a pattern is defined. Unfortunately, it is not entirely compatible with the SQL:2016 standard.

There is an initiative in Impala: IMPALA-4018 and Hive HIVE-21575 to support SQL:2016 patterns as both these systems lack this feature currently.
The Impala design document and compatibility matrix created by Gabor Kaszab covers how the compliance with the standard can be achieved and how do other databases support this standard. I believe these documents are very useful for Spark community as well.
Since Hive is also JVM based I also see an option to share some code that does the parsing/formatting according to the standard.

(Please note that format patterns are just one part of these tickets, the other part about `CAST (... FORMAT <pattern>)` function, which is also part of the SQL:2016 standard, is covered here: SPARK-27881)