Subtract n months from a date field in hive

I am trying to limit a table by a specific number of months, 24 for example in my WHERE clause. I have used the following with no success:

Where month(EVENT_START_DT_TM) > add_months(from_unixtime(unix_timestamp()),-1)

EVENT_START_DT_TM is a true datetime field and I want to have a 24 month look back period from the current date matching up against that field. Looking at the HIVE manual, a lot of date functions require date STRINGS, but what a true DATE field?

1 Answer
1

use Current_date() and add_months() function to get date before 24 months then use date_format() function to change the format as per your needs(matches to EVENT_START_DT_TM
field format)

hive> select date_format(add_months(current_date(),-24),"yyyy-MM-dd HH:mm:ss.SSS");
+--------------------------+--+
| _c0 |
+--------------------------+--+
| 2016-06-29 00:00:00.000 |
+--------------------------+--+


True date field is when your data in date field matches with yyyy-MM-dd format then you can extract months from the field

hive> select month("2018-10-12");
+------+--+
| _c0 |
+------+--+
| 10 |
+------+--+


if field values are not true date field i.e not in yyyy-MM-dd format

hive> select month("20181012");
+-------+--+
| _c0 |
+-------+--+
| NULL |
+-------+--+


we cannot extract month directly by using months function instead we need to change the format of the field then extract the month by using from_unixtime/unix_timestamp..etc functions (or) using substring functions

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.