Impala - Using Time in where clause and time from timestamp

[#1]

Hi,

I currenty have a timestamp field converted to a timestamp via cast(a.timefield as timestamp).

I need to be able to extract the time from the timestamp and then use this in the where clause to filter on results with time between 20:30:00 and 08:00:00.

Any assistance appreciated.

many thanks.

[#2 · Accepted solution]

Lets assume your timestamp column is "t", you just calculate the total minutes for the given day from the timestamp and then filter with OR - either it is more than 20*60 +30 (20:30:00) or is less than 8*60 (08:00:00).

select * from
mytable
where ( date_part('hour', t)* 60 + date_part( 'minute', t) ) >= (20*60+30) or 
( date_part('hour', t)* 60 + date_part( 'minute', t) ) < (8*60)

[#3]

Tomas that's great. Exactly what I was after. Many thanks.