CODEWITHSUNDEEP
hive
activelearner
activelearner

Reputation: 7745

Hive Query to get records between two dates of string type

I am trying to query a table with the fields st_date and end_date on Hue. These fields take in string type values e.g. '2014-04-04', '2009-10-10' etc. Suppose, I want to find records between st_date = 2014-04-04' and end_date = '2014-10-10' with both the dates included:

How would I write a query to retrieve records WHERE st_date > 2014-04-03 and end_date < 2014-10-09?

More specifically, I am facing problems related to the date conversion in this query.

Upvotes: 10

Views: 26393

Answers (3)

Saurabh
Saurabh

Reputation: 31

hive> select * from salesdata01 where from_unixtime(unix_timestamp(Order_date, 'dd-MM-yyyy'),'yyyy-MM-dd') >= from_unixtime(unix_timestamp('2010-09-01', 'yyyy-MM-dd'),'yyyy-MM-dd') and from_unixtime(unix_timestamp(Order_date, 'dd-MM-yyyy'),'yyyy-MM-dd') <= from_unixtime(unix_timestamp('2011-09-01', 'yyyy-MM-dd'),'yyyy-MM-dd') limit 10;

OK
1   3   13-10-2010  Low 6.0 261.54  0.04    Regular Air -213.25 38.94
80  483 10-07-2011  High    30.0    4965.7593   0.08    Regular Air 1198.97 195.99
97  613 17-06-2011  High    12.0    93.54   0.03    Regular Air -54.04  7.3
98  613 17-06-2011  High    22.0    905.08  0.09    Regular Air 127.7   42.76
103 643 24-03-2011  High    21.0    2781.82 0.07    Express Air -695.26 138.14
127 807 23-11-2010  Medium  45.0    196.85  0.01    Regular Air -166.85 4.28
128 807 23-11-2010  Medium  32.0    124.56  0.04    Regular Air -14.33  3.95
160 995 30-05-2011  Medium  46.0    1815.49 0.03    Regular Air 782.91  39.89
229 1539    09-03-2011  Low 33.0    511.83  0.1 Regular Air -172.88 15.99
230 1539    09-03-2011  Low 38.0    184.99  0.05    Regular Air -144.55 4.89
Time taken: 0.166 seconds, Fetched: 10 row(s)

hive> select * from salesdata01 where from_unixtime(unix_timestamp(Order_date, 'dd-MM-yyyy'),'yyyy-MM-dd') >= from_unixtime(unix_timestamp('2010-09-01', 'yyyy-MM-dd'),'yyyy-MM-dd') and from_unixtime(unix_timestamp(Order_date, 'dd-MM-yyyy'),'yyyy-MM-dd') <= from_unixtime(unix_timestamp('2010-12-01', 'yyyy-MM-dd'),'yyyy-MM-dd') limit 10;

OK
1   3   13-10-2010  Low 6.0 261.54  0.04    Regular Air -213.25 38.94
127 807 23-11-2010  Medium  45.0    196.85  0.01    Regular Air -166.85 4.28
128 807 23-11-2010  Medium  32.0    124.56  0.04    Regular Air -14.33  3.95
256 1792    08-11-2010  Low 28.0    370.48  0.04    Regular Air -5.45   13.48
381 2631    23-09-2010  Low 27.0    1078.49 0.08    Regular Air 252.66  40.96
656 4612    19-09-2010  Medium  9.0 89.55   0.06    Regular Air -375.64 4.48
769 5506    07-11-2010  Critical    22.0    129.62  0.05    Regular Air 4.41    5.88
1457    10499   16-11-2010  Not Specified   29.0    6250.936    0.01    Delivery Truck  31.21   262.11
1654    11911   10-11-2010  Critical    25.0    397.84  0.0 Regular Air -14.75  15.22
2323    16741   30-09-2010  Medium  6.0 157.97  0.01    Regular Air -42.38  22.84
Time taken: 0.17 seconds, Fetched: 10 row(s)

Upvotes: 0

dpsdce
dpsdce

Reputation: 5460

Query

   WHERE st_date > '2014-04-03' and end_date < '2014-10-11'

should give you desired result because even if it is a sting a it will be compared lexicographically i.e '2014-04-04' will be always greater '2014-04-03'.

I ran it on my sample tables and it works perfectly fine.

Upvotes: 9

srinivasan Hariharan
srinivasan Hariharan

Reputation: 365

Try below query.

SELECT *
FROM TABLE
WHERE CAST(TRANSLATE(st_date,"-","") AS BIGINT) > CAST(TRANSLATE("2014-04-03","-","") AS BIGINT)  AND CAST(TRANSLATE(end_date,"-","") AS BIGINT) < CAST(TRANSLATE("2014-10-09","-","") AS BIGINT)

Upvotes: 1

Related Questions