Date comparison in Hive

Question

I'm working with Hive and I have a table structured as follows:

CREATE TABLE t1 (
  id INT,
  created TIMESTAMP,
  some_value BIGINT
);

I need to find every row in t1 that is less than 180 days old. The following query yields no rows even though there is data present in the table that matches the search predicate.

select * 
from t1 
where created > date_sub(from_unixtime(unix_timestamp()), 180);

What is the appropriate way to perform a date comparison in Hive?

score 14 · Accepted Answer · answered Dec 28 '12 at 17:54

How about:

where unix_timestamp() - created < 180 * 24 * 60 * 60

Date math is usually simplest if you can just do it with the actual timestamp values.

Or do you want it to only cut off on whole days? Then I think the problem is with how you are converting back and forth between ints and strings. Try:

where created > unix_timestamp(date_sub(from_unixtime(unix_timestamp(),'yyyy-MM-dd'),180),'yyyy-MM-dd')

Walking through each UDF:

unix_timestamp() returns an int: current time in seconds since epoch
from_unixtime(,'yyyy-MM-dd') converts to a string of the given format, e.g. '2012-12-28'
date_sub(,180) subtracts 180 days from that string, and returns a new string in the same format.
unix_timestamp(,'yyyy-MM-dd') converts that string back to an int

If that's all getting too hairy, you can always write a UDF to do it yourself.

Fantastic explanation of the solution I ended up using. One question - will the comparison between `TIMESTAMP`/`STRING` and an `INT` work out in this case? — , Dec 28 '12 at 19:17
Following up - the `TIMESTAMP`/`STRING` to `INT` compare works. Thanks for that information, it simplifies the query nicely. — , Dec 28 '12 at 19:25

score 6 · Answer 2 · answered Dec 29 '12 at 00:55

Alternatively you may also use datediff. Then the where clause would be
in case of String timestamp (jdbc format) :

datediff(from_unixtime(unix_timestamp()), created) < 180;

in case of Unix epoch time:

datediff(from_unixtime(unix_timestamp()), from_unixtime(created)) < 180;

score 3 · Answer 3 · answered Dec 29 '12 at 00:52

I think maybe it's a Hive bug dealing with the timestamp type. I've been trying to use it recently and getting incorrect results. If I change your schema to use a string instead of timestamp, and supply values in the

yyyy-MM-dd HH:mm:ss

format, then the select query worked for me.

According to the documentation, Hive should be able to convert a BIGINT representing epoch seconds to a timestamp, and that all existing datetime UDFs work with the timestamp data type.

with this simple query:

select from_unixtime(unix_timestamp()), cast(unix_timestamp() as timestamp) from test_tt limit 1;

I would expect both fields to be the same, but I get:

2012-12-29 00:47:43 1970-01-16 16:52:22.063

I'm seeing other weirdness as well.

It looks like this is reported in [HIVE-3454](https://issues.apache.org/jira/browse/HIVE-3454), but there hasn't been any work done to fix it as the Hive maintainers want to ensure backwards compatibility with the broken behavior. — , Dec 29 '12 at 16:10
@JeremiahPeschka Thanks, plus has workaround... I should search for my other timestamp issues there as well. — libjack, Dec 29 '12 at 18:44

score 3 · Answer 4 · answered Oct 27 '13 at 21:40

3

TIMESTAMP is milliseconds
unix_timestamp is in seconds
You need to multiply the RHS by 1000.

where created > 1000 * date_sub(from_unixtime(unix_timestamp()), 180);

answered Oct 27 '13 at 21:40

user1089933

87
1
2

score 1 · Answer 5 · edited May 23 '17 at 12:24

After reviewing this and referring to Date Difference less than 15 minutes in Hive I came up with a solution. While I'm not sure why Hive doesn't perform the comparison effectively on dates as strings (they should sort and compare lexicographically), the following solution works:

FROM (
    SELECT  id, value,
            unix_timestamp(created) c_ts, 
            unix_timestamp(date_sub(from_unixtime(unix_timestamp()), 180), 'yyyy-MM-dd') c180_ts
    FROM    t1
) x
JOIN t1 t ON x.id = t.id
SELECT  to_date(t.Created), 
        x.id, AVG(COALESCE(x.HighestPrice, 0)), AVG(COALESCE(x.LowestPrice, 0))
WHERE   unix_timestamp(t.Created) > x.c180_ts
GROUP BY to_date(t.Created), x.id ;

Date comparison in Hive

5 Answers5