Geek Answers Handbook

Hive Query for getting records between two string type dates

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

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

In particular, I ran into problems with date conversion in this query.

+7
hive
source share
3 answers

Request

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

should give you the desired result, because even if it is a bite, it will be compared lexicographically. I '2014-04-04' will always be more than '2014-04-03' .

I ran it on my test tables and it works great.

+4
source share

Try to complete the request.

 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)
+1
source share
 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)
0
source share

More articles:


All Articles