[IMPALA-1734] cast(<string> as timestamp) should not require zero padded elements

• Type: Improvement
• Status: Closed
• Priority: Major
• Resolution: Duplicate
• Affects Version/s: Impala 2.1
• Fix Version/s: Product Backlog
• Labels: usability
• Epic Link: Timestamp Compatibility
• Assignee: Unassigned
• Reporter: Denys Lamanov
• Created: 03/Feb/15 11:04
• Updated: 20/Jun/17 22:33
• Resolved: 20/Jun/17 22:33

Description

When casting strings to timestamps, it should not be necessary to left pad values with zeros.

From the ANSI SQL/foundation spec

<date value> ::=
  <years value> <minus sign> <months value> <minus sign> <days value>

<time value> ::=
  <hours value> <colon> <minutes value> <colon> <seconds value>

Examples that do not work that should

select version()
+-----------------------------------------------------------------------------------------+
| version()                                                                               |
+-----------------------------------------------------------------------------------------+
| impalad version 2.6.0-cdh5.8.0 RELEASE (build 5464d1750381b40a7e7163b12b09f11b891b4de3) |
| Built on Thu, 16 Jun 2016 12:43:48 PST                                                  |
+-----------------------------------------------------------------------------------------+

select cast('2015-1-01' as timestamp) as ts;
+------+
| ts   |
+------+
| NULL |
+------+

select cast('2015-01-1' as timestamp) as ts;
+------+
| ts   |
+------+
| NULL |
+------+

select cast('2016-1-1 9:5:7' as timestamp) as ts
+------+
| ts   |
+------+
| NULL |
+------+

Issue Links

• duplicates: IMPALA-926 Support more timestamp formats in casts
• is related to: IMPALA-5315 Cast to timestamp fails for YYYY-M-D format
• relates to: IMPALA-4017 support ANSI timestamp literal casts
• relates to: IMPALA-4018 Add support for SQL:2016 datetime templates/patterns/masks to CAST(... AS ... FORMAT <template>)