Search code examples
sqldatehivehqlbeeline

Date function in Beeline Where Clause

I am trying to add a date function in my automation script to run previous month's data every month. When I try to run below query separately it works.

Select add_months(trunc(FROM_UNIXTIME(UNIX_TIMESTAMP()),'MM'),-1),  
date_sub(concat(from_unixtime(unix_timestamp(), 'yyyy-MM'), '-01'), 1);
+-------------+-------------+--+
|     _c0     |     _c1     |
+-------------+-------------+--+
| 2018-03-01  | 2018-03-31  |
+-------------+-------------+--+
1 row selected (6.697 seconds)

But, when I try to add the same in a 'Where' filter in my beeline query, it does not throw any error and stop but the query takes a very long time to execute (hours and hours) and finally kills one-by-one. 

where date_vairable between add_months(trunc(FROM_UNIXTIME(UNIX_TIMESTAMP()),'MM'),-1) and  
date_sub(concat(from_unixtime(unix_timestamp(), 'yyyy-MM'), '-01'), 1);

Help is very much appreciated.. Thanks in Advance!

Solution

  • Assuming your where clause should filter for rows from the previous month, use

    where date_vairable between 
add_months(trunc(FROM_UNIXTIME(UNIX_TIMESTAMP()),'MM'),-1) and  
date_sub(trunc(FROM_UNIXTIME(UNIX_TIMESTAMP()),'MM'),1)

    or express this more succinctly as 

    date_vairable between add_months(trunc(current_date,'MM'),-1)
and date_add(trunc(current_date,'MM'),-1)