9

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.

lospejos
  • 1,976
  • 3
  • 19
  • 35
darkpool
  • 13,822
  • 16
  • 54
  • 89

1 Answers1

22

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.

  • Thanks. I wasn't aware of how to cast which was why I was struggling. – darkpool Jul 28 '15 at 06:37
  • 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? – mohitsharma44 Feb 23 '17 at 06:48
  • 3
    `between current_date + time '16:00' and (current_date + 1)::timestamp` –  Feb 23 '17 at 07:04
  • see this about using `between` https://wiki.postgresql.org/wiki/Don't_Do_This#Don.27t_use_BETWEEN_.28especially_with_timestamps.29 –  Curtis Feb 08 '22 at 08:37
  • @Curtis: I am not using `between` with a timestamp. And I pointed out the downsides of using it. –  Feb 08 '22 at 08:41