CODEWITHSUNDEEP
hadoophivehiveql
Holmes
Holmes

Reputation: 1079

How to calculate Date difference in Hive

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!

Upvotes: 35

Views: 194819

Answers (4)

Kishore
Kishore

Reputation: 5891

datediff(to_date(String timestamp), to_date(String timestamp))

For example:

SELECT datediff(to_date('2019-08-03'), to_date('2019-08-01')) <= 2;

Upvotes: 69

Ward W
Ward W

Reputation: 678

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

Upvotes: 10

Sambit Tripathy
Sambit Tripathy

Reputation: 444

I would try this first

select * from employee where month(current_date)-3 = month(joining_date)

Upvotes: 1

maxymoo
maxymoo

Reputation: 36555

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 ;)

Upvotes: 2

Related Questions