34

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!

Holmes
  • 1,059
  • 2
  • 17
  • 25

4 Answers4

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

For example:

SELECT datediff(to_date('2019-08-03'), to_date('2019-08-01')) <= 2;
PALEN
  • 2,784
  • 2
  • 23
  • 24
Kishore
  • 5,761
  • 5
  • 28
  • 53
  • 3
    `to_date` is unnecessary if the column is already in 'yyyy-mm-dd' format. – damientseng Jan 04 '20 at 06:44
  • 1
    `The DATEDIFF function returns the number of days between the two given dates` [hadoop tutorial](http://hadooptutorial.info/hive-date-functions/#DATEDIFF_string_date1_string_date2) – Henrique Brisola Dec 13 '21 at 13:47
10

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

Ward W
  • 640
  • 6
  • 14
2

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

maxymoo
  • 35,286
  • 11
  • 92
  • 119
  • Thanks for you reply. In the Language Manual, it specified that the datediff function calculate difference between string types. Does it work for Timestamp type as well? – Holmes May 29 '15 at 05:47
  • I don't know, I never have used the timestamp type, I always just use string for my timestamp columns. If it doesn't work, just cast your timestamp column to string (or leave it as string in the first place). – maxymoo May 29 '15 at 05:54
  • DATEDIFF() only takes STRING/TIMESTAMP/DATEWRITABLE types, I suggest only use the format : YYYY-MM-DD – staticor Dec 30 '19 at 02:50
1

I would try this first

select * from employee where month(current_date)-3 = month(joining_date)
Sambit Tripathy
  • 434
  • 1
  • 4
  • 14
  • what about those who joined less than 3 month ago? What about those who joined in Jan - March? – aviad May 29 '15 at 07:02
  • if today is may, he wants those who joined in april, march and feb, similarly for june it would be may, april and march – Sambit Tripathy May 29 '15 at 07:13
  • try to run your query with current_date=2015-01-01 and joining_date=2014-12-01 what would be the result? – aviad May 29 '15 at 07:16
  • (date_trunc('MONTH',idate)- INTERVAL '1 MONTH ')::Date + (to_char(idate,'DD')::int -1); please help us – Developer KE Oct 25 '21 at 08:59