2

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

leftjoin
  • 36,950
  • 8
  • 57
  • 116
capucine58
  • 21
  • 2
  • to_date function returns a string. Is this what you intended? In such a case you are trying to compare two strings. If you want to do date comparisons (as in the arithmetic sense), you may want to try unix_timestamp function, instead. It will give you a number and you can compare two numbers and decide which one is bigger and which one is smaller. – Amit Jul 26 '19 at 15:33

1 Answers1

2

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'
leftjoin
  • 36,950
  • 8
  • 57
  • 116