Select timestamp data for specific time range each day

Question

I have a postgresql table with a column of type timestamp without timezone. Every row increases/decreases by 1 minute eg:

2015-07-28 01:35:00
2015-07-28 01:34:00
2015-07-28 01:33:00
...
...
2015-07-27 23:59:00
2015-07-27 23:58:00
2015-07-27 23:57:00

I am trying to write a query that will select all the rows between a certain date range, but also during a specific time range for those days, eg: Select all rows between 2015-05-20 to 2015-06-20 during 08:00:00 to 16:00:00.

Everything i've tried so far doesn't seem to take both the date + time requirement into account.

Answer 1

I'm not sure I understand you correctly, but if you want all rows for the days specified, but exclude rows from those days where the time part is outside of 08:00 to 16:00 the following should do it:

select *
from the_table
where the_column::date between date '2015-05-20' and date '2015-06-20'
  and the_column::time between time '08:00::' and '16:00:00'

The expression the_column::date is called a cast and will convert the timestamp to a date removing the time information. the_column::time extracts the time part of the timestamp column.

The between operator will include the boundaries (e.g. rows with a time precisely at 16:00:00). If you don't want that you will need to change the between condition to a corresponding > and < condition.