Menu
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.
404
datediff function in Hive takes 2 dates in String type and gives you the difference between the dates.
Hive Date and Timestamp functions are used to manipulate Date and Time on HiveQL queries over Hive CLI, Beeline, and many more applications Hive supports. The default date format of Hive is yyyy-MM-dd , and for Timestamp yyyy-MM-dd HH:mm:ss .
Hive supports two data types for Date/Time-related fields— Timestamp and Date : The Timestamp data type is used to represent a particular time with the date and time value. It supports variable-length encoding of the traditional UNIX timestamp with an optional nanosecond precision. It supports different conversions.
The DATEDIFF function returns the number of days between the two given dates.
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.
175
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)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
