CODEWITHSUNDEEP
sqlhadoophivedate-difference
displayname
displayname

Reputation: 327

How to get date difference in minutes using Hive

Below query is my sql server query and I want it to convert it into hive query:

select DATEDIFF([minute], '19000101', '2013-01-01 10:10:10')

Upvotes: 16

Views: 52255

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175566

You could use unix_timestamp for dates after 1970:

SELECT (unix_timestamp('2013-01-01 10:10:10') 
      - unix_timestamp('1970-01-01 00:00:00'))/60
  1. Convert both dates to seconds from 1970-01-01
  2. Substract them
  3. Divide by 60 to get minutes

EDIT:

Adding Minutes: change date to unixtime -> add var * 60sec -> convert back to date

SELECT from_unixtime(unix_timestamp('2013-01-01 10:10:10') + 10 * 60) AS result

db<>fiddle demo using MySQL

Upvotes: 27

Related Questions