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...
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!
4 Answers
datediff(to_date(String timestamp), to_date(String timestamp))
For example:
SELECT datediff(to_date('2019-08-03'), to_date('2019-08-01')) <= 2;
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:
yes datediff is implemented; see:
By the way I found this by Google-searching "hive datediff", it was the first result ;)
I would try this first
select * from employee where month(current_date)-3 = month(joining_date)
Chloe Bennett explores the intersection of pop culture, streaming entertainment, digital trends, and contemporary lifestyle. Her weekly commentary reaches thousands of culture enthusiasts.
