question Re: Hive - why doesn't truncate support a DD/Day value? in Archives of Support Questions (Read Only)

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

andrewg — Tue, 05 Jan 2016 22:32:16 GMT

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.

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

andrewg — Tue, 05 Jan 2016 22:42:56 GMT

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?

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

andrewg — Wed, 06 Jan 2016 00:46:46 GMT

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

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

Aanch — Wed, 28 Oct 2020 10:21:13 GMT

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}'