Impala String Date - greater than or less than command to query tables

[#1]

Hi All,

I have a date which is stored as string and we would like to query it in Impala using greater than or less than functionality , however i am having no luck and when i play around unixtimestamp/timestamp

it returns as Null values.

e.g. select * from table T1 where orderdate is less than < 1/1/2001

order date is stored as string in T1 and doesnot have padded zeroes : 1/1/2001 (and not 01/01/2001)

SELECT
cast(unix_timestamp(`date`, "MM/dd/yyyy") as timestamp), `date` FROM T1 ; 

Result --  Null values for cast command

SELECT * FROM T1 WHERE start = '"4/3/2015"' ;

Result - No data

SELECT * FROM T1 WHERE start > "4/3/2015" ;

Result - No data

[#2 · Accepted solution]

Hi, it is not so common to have a date in this format, but it is possible to convert it into timestamp in Impala.

Your approach was good, just used a wrong format:

> CORRECT FORMAT
[10.197.0.1:21000] > select *, cast( unix_timestamp(s, "M/d/yyyy") as timestamp ) from work.t;
+-----+------------+--------------------------------------------------+
| id  | s          | cast(unix_timestamp(s, 'm/d/yyyy') as timestamp) |
+-----+------------+--------------------------------------------------+
| 100 | 12/24/2018 | 2018-12-24 00:00:00                              |
| 200 | 3/24/2018  | 2018-03-24 00:00:00                              |
+-----+------------+--------------------------------------------------+
Fetched 2 row(s) in 0.33s

> THIS IS A WRONG FORMAT
[10.197.0.1:21000] > select *, cast( unix_timestamp(s, "MM/dd/yyyy") as timestamp ) from work.t;
+-----+------------+----------------------------------------------------+
| id  | s          | cast(unix_timestamp(s, 'mm/dd/yyyy') as timestamp) |
+-----+------------+----------------------------------------------------+
| 100 | 12/24/2018 | 2018-12-24 00:00:00                                |
| 200 | 3/24/2018  | NULL                                               |
+-----+------------+----------------------------------------------------+

Convert the string to a date and then you can use a correct where conditions.

[#3]

Thank you 🙂 that resolved it for me.