CODEWITHSUNDEEP
sqlhive
Tiago Fernandes
Tiago Fernandes

Reputation: 23

Comparing dates in hive not returning what expected

I'm doing some comparison with dates, and when I do the following:

from_unixtime(unix_timestamp(a11.duedate),'dd-MM-yyyy') < 
from_unixtime(unix_timestamp(),'dd-MM-yyyy')

returns false

The a11.duedate is 21-02-2018 and is returning false when comparing with today's date.

When I compare it in miliseconds, returns true:

unix_timestamp(a11.duedate) < unix_timestamp()

What I am missing here? In my view, both conditions should return true.

Upvotes: 2

Views: 2441

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270993

Why are you doing all this conversion? There is no need to go to unix timestamps for this. Assuming the value is stored as a date (which it should be):

a11.duedate < CURRENT_DATE()

If it is not stored as a date, then I believe you can do:

unix_timestamp(a11.duedate, 'dd-MM-yyyy') < unix_timestamp(current_date())

Or, if you want to practice arithmetic:

unix_timestamp(a11.duedate, 'dd-MM-yyyy') < floor(current_timestamp / (24*60*60)) * 24*60*60

Upvotes: 2

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49270

Use the standard yyyy-MM-dd comparison. Note that the return type of from_unixtime is string and hence you get false (because of the string comparison) with the query you run.

from_unixtime(unix_timestamp(a11.duedate,'dd-MM-yyyy'),'yyyy-MM-dd')
< from_unixtime(unix_timestamp(),'yyyy-MM-dd')

One more option is compare the unix timestamps rather than converting them to date strings.

Upvotes: 1

Related Questions