Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

Hive - why doesn't truncate support a DD/Day value?

Solved Go to solution

Hive - why doesn't truncate support a DD/Day value?

From the https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-DateFunctions

trunc(string date, string format)

I'm executing these commands in beeline:

0: jdbc:hive2://> select trunc(current_timestamp(), 'MM');
OK
+-------------+--+
|     _c0     |
+-------------+--+
| 2016-01-01  |
+-------------+--+
1 row selected (0.97 seconds)
0: jdbc:hive2://> select trunc(current_timestamp(), 'DD');
OK
+-------+--+
|  _c0  |
+-------+--+
| NULL  |
+-------+--+
1 row selected (0.241 seconds)

Now, the question is why doesn't it support a day value? This is such a natural function for truncating timestamps to a day only.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

Re: Hive - why doesn't truncate support a DD/Day value?

Through experiments I found this one works as expected:

jdbc:hive2://> select date_sub(current_timestamp(), 0);
OK
+-------------+--+
|     _c0     |
+-------------+--+
| 2016-01-05  |
+-------------+--+

Which is much harder than it should be. Given how much legacy SQL there is relying on trunc(timestamp), can we enhance the Hive's function to throw the timestamp out too maybe?

View solution in original post

2 REPLIES 2
Highlighted

Re: Hive - why doesn't truncate support a DD/Day value?

Through experiments I found this one works as expected:

jdbc:hive2://> select date_sub(current_timestamp(), 0);
OK
+-------------+--+
|     _c0     |
+-------------+--+
| 2016-01-05  |
+-------------+--+

Which is much harder than it should be. Given how much legacy SQL there is relying on trunc(timestamp), can we enhance the Hive's function to throw the timestamp out too maybe?

View solution in original post

Highlighted

Re: Hive - why doesn't truncate support a DD/Day value?

I found that to_date() function generates correct data types which will work correctly with comparisons and ordering.

Don't have an account?
Coming from Hortonworks? Activate your account here