[DRILL-3284] Document incompatibility between drill's to_date and hive's unix_timestamp

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.2.0
• Component/s: Documentation, Functions - Hive
• Assignee: Kris Hahn
• Reporter: Rahul Kumar Challapalli
• Created: 12/Jun/15 18:07
• Updated: 11/Sep/15 17:38
• Resolved: 06/Aug/15 19:12

Description

The below query from drill produces wrong results because unix_timestamp (function from hive) returns the value in seconds while to_date treats its input in milliseconds.

select to_date(unix_timestamp('1998-05-06', 'yyyy-MM-dd')) from dummy limit 1;
+-------------+
|   EXPR$0    |
+-------------+
| 1970-01-11  |
+-------------+

In order to make this work we should use the below query

select to_date(unix_timestamp('1998-05-06', 'yyyy-MM-dd')*1000) from dummy limit 1;
+-------------+
|   EXPR$0    |
+-------------+
| 1998-05-06  |
+-------------+

If this is not a bug on drill's side, we should atleast document this