CODEWITHSUNDEEP
datehivetimestamphiveqlhive-query
capucine58
capucine58

Reputation: 21

Hive queries with dates

I have a little problem, I would like to filter a date with hive query but the output is empty. My column is string type

I tried this : 

select * from my_table 
where to_date(date) < to_date('01/08/19 00:00:00')

The format of my column date is 01/08/19 18:00:00

Upvotes: 2

Views: 1620

Answers (1)

leftjoin
leftjoin

Reputation: 38335

Dates in this format '01/08/19 00:00:00' are not in comparable format because in such format '02/08/19 00:00:00' is greater than '01/08/20 00:00:00'.

Use unix_timestamp and from_unixtime to convert to the comparable format ('yyyy-MM-dd HH:mm:ss'), then compare with date in the same format.

select * from my_table 
where from_unixtime(unix_timestamp(date,'dd/MM/yy HH:mm:ss'),'yyyy-MM-dd HH:mm:ss') < '2019-08-01 00:00:00'

Upvotes: 2

Related Questions