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

[#1]

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.

[#2 · Accepted solution]

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?

[#3]

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

[#4]

Hi there,

I was able to get this to work in my select but not in my where clause - any advice?  Thank you in advance!

Select

date_sub(start_time, 0) ,

anchor_id

From a.b

where

date = max_pt(a.b)

and date_sub(start_time, 0) >= '${date-28}'