CODEWITHSUNDEEP
sqlhivedatediffapache-drilldbeaver
Surya
Surya

Reputation: 51

how to calculate date difference and get the output in days?

I am trying to calculate difference between two dates in dbeaver connected to apache drill.

ex:

date 1  1/27/2019
date 2  2/01/2019

output 1 in terms of days output 2 in terms of weeks

Upvotes: 1

Views: 6007

Answers (2)

Vijiy
Vijiy

Reputation: 1197

if date is in string format - 

select datediff(
(from_unixtime(unix_timestamp(enddate, dateformat), dateformat)) -
(from_unixtime(unix_timestamp(startdate, dateformat), dateformat)));

Ex - hive> select datediff(from_unixtime(unix_timestamp('2019-01-28', 'yyyy-MM-dd'), 'yyyy-MM-dd'), from_unixtime(unix_timestamp('2019-01-27', 'yyyy-MM-dd'), 'yyyy-MM-dd'));

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269493

Well, have you tried datediff()?

select datediff(date1, date2) as days_between
       floor(datediff(date1, date2) / 7) as weeks_between

Upvotes: 1

Related Questions