[IMPALA-2262] Improve timestamp format parser to support more formats and make it consistent with Hive

• Type: Improvement
• Status: Open
• Priority: Minor
• Resolution: Unresolved
• Affects Version/s: Impala 2.2.4
• Component/s: Backend
• Target Version: Product Backlog
• Assignee: Unassigned
• Reporter: Bharath Vissapragada
• Created: 28/Aug/15 06:29
• Updated: 24/Dec/20 00:00

Description

Setup:

create table string_time_table (date_last_changed string);
insert into string_time_table values ('06/09/2006 01:25 PM');

Hive:

SELECT date_last_changed, FROM_UNIXTIME(UNIX_TIMESTAMP(date_last_changed,'MM/dd/yyyy hh:mm a')) FROM string_time_table; 
+----------------------+----------------------+--+ 
| date_last_changed | _c1 | 
+----------------------+----------------------+--+ 
| 06/09/2006 01:25 PM | 2006-06-09 13:25:00 | 
+----------------------+----------------------+--+ 

Impala:

SELECT date_last_changed, FROM_UNIXTIME(UNIX_TIMESTAMP(date_last_changed,'MM/dd/yyyy hh:mm a')) FROM string_time_table; 
Query: select date_last_changed, FROM_UNIXTIME(UNIX_TIMESTAMP(date_last_changed,'MM/dd/yyyy hh:mm a')) FROM string_time_table 
WARNINGS: Bad date/time conversion format: MM/dd/yyyy hh:mm a 

We don't support am/pm based formats [1]. I think we should make impala's usage consistent with hive

[1] https://github.com/cloudera/Impala/blob/cdh5-trunk/be/src/runtime/timestamp-parse-util.h#L205