Support Questions

# Impala - Using Time in where clause and time from timestamp Hazard
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 Tomas79
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)```
2 REPLIES 2

# Re: Impala - Using Time in where clause and time from timestamp Tomas79
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)```

# Re: Impala - Using Time in where clause and time from timestamp Hazard
New Contributor
Tomas that's great. Exactly what I was after. Many thanks.
Don't have an account?
Announcements
What's New @ Cloudera
What's New @ Cloudera
What's New @ Cloudera