12-11-2018 07:37 AM - edited 12-11-2018 07:38 AM
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.
12-11-2018 09:01 AM
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)