How to calculate Date difference in Hive

Question

I m a novice  I have a employee table with a column specifying the joining date and I want to retrieve the list of employees who have joined in the last 3 months  I understand we can get the current date using from unixtime unix timestamp     How do I calculate the datediff  Is there a built in DATEDIFF   function like in MS SQL  please advice

User · Answer

yes datediff is implemented  see  https   cwiki apache org confluence display Hive LanguageManual UDF  By the way I found this by Google-searching  hive datediff   it was the first result

User · Answer

datediff to date String timestamp   to date String timestamp     For example   SELECT datediff to date  2019-08-03    to date  2019-08-01     lt   2

User · Answer

If you need the difference in seconds  i e   you re comparing dates with timestamps  and not whole days   you can simply convert two date or timestamp strings in the format  YYYY-MM-DD HH MM SS   or specify your string date format explicitly  using unix timestamp    and then subtract them from each other to get the difference in seconds   And can then divide by 60 0 to get minutes  or by 3600 0 to get hours  etc    Example    UNIX TIMESTAMP  2017-12-05 10 01 30   - UNIX TIMESTAMP  2017-12-05 10 00 00   AS time diff -- This will return 90  seconds   Unix timestamp converts string dates into BIGINTs     More on what you can do with unix timestamp   here  including how to convert strings with different date formatting  https   cwiki apache org confluence display Hive LanguageManual UDF LanguageManualUDF-DateFunctions

User · Answer

I would try this first  select   from employee where month current date -3   month joining date

[hadoop] How to calculate Date difference in Hive

Examples related to hadoop

Examples related to hive

Examples related to hiveql