Hi all,
I'm trying to generate a query from a dataset which includes a timestamp column. I want to extract rows where the hour portion is between a start and end hour.

It all works fine provided the start hour is less than the end hour but I can't see how to get it say for

starthour=21
endhour=3

where I want records between 9pm and 3am, regardless as to the day.

Now I could see adding some logic where I first check if start < end, then check from 21 to 0 plus 0 to 3 but thought maybe there was some better way.

I'm using date_part('hour',mytimecol) >= starthour and date_part('hour',mytimecol) < endhour

If I did need the logic to check which is greater, I'm not entirely sure how I would do it in sql anyway.

Thanks

In case your basis of time is a day, then this solution scans as per a given timestamp and compares with current datetime

select col1, mytimecol from table1 where EXTRACT(EPOCH FROM etime) > EXTRACT(EPOCH FROM current_timestamp)-(24*3600)-1
and EXTRACT(EPOCH FROM etime) < EXTRACT(EPOCH FROM current_timestamp)+(24*3600)+1
order by col1