question Impala - Using Time in where clause and time from timestamp in Archives of Support Questions (Read Only)

Impala - Using Time in where clause and time from timestamp

Hazard — Fri, 16 Sep 2022 13:58:38 GMT

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.

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

Tomas79 — Tue, 11 Dec 2018 17:01:21 GMT

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)

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

Hazard — Wed, 12 Dec 2018 08:58:37 GMT

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