Reply
New Contributor
Posts: 2
Registered: ‎12-11-2018
Accepted Solution

Impala - Using Time in where clause and time from timestamp

[ Edited ]

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.

Master
Posts: 402
Registered: ‎07-01-2015

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

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)
Highlighted
New Contributor
Posts: 2
Registered: ‎12-11-2018

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

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