Support Questions

Find answers, ask questions, and share your expertise

Impala - Using Time in where clause and time from timestamp

avatar
New Contributor

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.

1 ACCEPTED SOLUTION

avatar

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)

View solution in original post

2 REPLIES 2

avatar

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)

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