Impala - How to compare date time type 'mm dd yy 00:00AM'

Question

In Impala, when I tried to compared the date, it will give wrong result. For example:

select 'Nov 23 2018  3:02AM' > 'Dec  1 2018 12:00AM'

which will return True

when use cast() function select cast('Dec 1 2018 12:00AM' as timestamp) which will give null. Thus, how can we compare the date in the format mm dd yy 00:00AM

I'm not an Impala expert, but I could not see any date function which can parse a partial month name. So, you might have to do this processing outside your database. — Tim Biegeleisen, Nov 29 '18 at 10:23
`select now()` will get the time type 'yy-mm-dd 00:00:00' such as 2018-11-29 21:24:50.815029000 — user3651247, Nov 29 '18 at 10:25

score 0 · Answer 1 · answered Nov 29 '18 at 10:28

0

You should use Impala implicit formating yyyy-MM-dd HH:mm:ss.SSS. In your case you should use for example

select cast('2018-11-23 03:02:00.000' as timestamp)

answered Nov 29 '18 at 10:28

Radim Bača

10,646
1
19
33

score 0 · Answer 2 · answered Nov 29 '18 at 11:49

0

You can use unix_timestamp(). I think the right format is:

select unix_timestamp('Nov 23 2018  3:02AM', 'MMM DD YYYY HH:mmPP')

answered Nov 29 '18 at 11:49

Gordon Linoff

1,242,037
58
646
786

This will cause error `Bad date/time conversion format: MMM DD YYYY HH:mmPP` and stop working. – user3651247 Nov 29 '18 at 12:11

Impala - How to compare date time type 'mm dd yy 00:00AM'

2 Answers2