Postgresql - Select between specific Time and Date Range

Question

I'm trying to get some data from my tables - I need only a particular time of day ( between 22:00 PM one night and 06:00 AM the next morning) over a fixed date range.

This is what I have so far:

create new_table
as select table.v1, table.localminute from table 
where date(table.localminute) between
date('2013-11-01 00:00:00-05') and date('2014-12-01 00:00:00-05') 
and hour(table.localminute) between 22 and 6

I'm not sure how to just get the data for between the hours of 22:00:00-05 and 06:00:00-05 each day.

I also thought about using wildcards for the dates but either it doesn't work or my implementation wasn't right. Any help will be appreciated!

I used info from the link below to set this up as it is - not sure what I need to do.

Select between date range and specific time range

Patrick · Accepted Answer · 2015-06-12 00:58:05Z

13

SELECT v1, localminute 
FROM "table" 
WHERE localminute BETWEEN '2013-11-01'::date AND '2014-12-01'::date 
AND (extract('hour' from localminute) >= 22 OR extract('hour' from localminute) < 6);

edited Jun 12, 2015 at 0:58

answered Jun 10, 2015 at 3:34

Patrick

33k7 gold badges73 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ashwin Shankar Over a year ago

Thanks! This helped point me in the right direction. Can't really extract "date" so this is how the final query looked for me: Found out that you can only extract parts of the timestamp like day, hour, year so removed that part from your query: Thanks again for your help!

Ashwin Shankar Over a year ago

Thanks! I would up vote the answer but I don't have enough credits yet.

Splitframe Over a year ago

Also works: WHERE EXTRACT('hour' FROM localminute) BETWEEN 2 AND 6 if the range happen to not go over midnight.

Collectives™ on Stack Overflow

Postgresql - Select between specific Time and Date Range

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related