Support Questions
Find answers, ask questions, and share your expertise

Impala - Using Time in where clause and time from timestamp

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

Accepted Solutions

Re: Impala - Using Time in where clause and time from timestamp

Master Collaborator

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

Re: Impala - Using Time in where clause and time from timestamp

Master Collaborator

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

Re: Impala - Using Time in where clause and time from timestamp

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