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.

Bradshaw

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: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.

Edlyn

Thanks. I wasn't aware of how to cast which was why I was struggling. – Bradshaw

Follow up question @a_horse_with_no_name .. I f I want to get the between 16:00:00 on the current day and 08:00:00 the next day, how can that be done? – Mindy

between current_date + time '16:00' and (current_date + 1)::timestamp – Edlyn

see this about using between wiki.postgresql.org/wiki/… – Adobe

@Curtis: I am not using between with a timestamp. And I pointed out the downsides of using it. – Edlyn