[IMPALA-2268] implicit casting of string to timestamp for functions

• Type: Improvement
• Status: Open
• Priority: Minor
• Resolution: Unresolved
• Affects Version/s: Impala 2.2
• Component/s: Frontend
• Labels: newbie usability
• Epic Link: Timestamp Compatibility
• Target Version: Product Backlog
• Assignee: Unassigned
• Reporter: Bharath Vissapragada
• Created: 31/Aug/15 08:54
• Updated: 01/Feb/21 00:39

Description

Consider date_add() builtin. string is automatically cast to a timestamp.

select date_add( "1900-01-01", 1 ) ; 
Query: select date_add( "1900-01-01", 1 ) 
+---------------------------+ 
| date_add('1900-01-01', 1) | 
+---------------------------+ 
| 1900-01-02 00:00:00 | 
+---------------------------+ 
Fetched 1 row(s) in 0.12s 

However with an "interval"

select date_add( '1900-01-01', interval 72 days ) ; 
Query: select date_add( '1900-01-01', interval 72 days ) 
ERROR: AnalysisException: Operand ''1900-01-01'' of timestamp arithmetic expression 'DATE_ADD('1900-01-01', INTERVAL 72 days)' returns type 'STRING'. Expected type 'TIMESTAMP'. 

We need to manually cast it to a timestamp, something like,

select date_add(cast("1900-01-01" as TIMESTAMP), interval 10 days ) ; 
Query: select date_add(cast("1900-01-01" as TIMESTAMP), interval 10 days ) 
+-------------------------------------------------------------+ 
| date_add(cast('1900-01-01' as timestamp), interval 10 days) | 
+-------------------------------------------------------------+ 
| 1900-01-11 00:00:00 | 
+-------------------------------------------------------------+ 
Fetched 1 row(s) in 0.02s 

Its convenient to make this behavior consistent across all builtins.

Issue Links

• is duplicated by: IMPALA-1503 Impala's date_sub and date_add functions are not automatically casting strings (in the correct format) to TIMESTAMP