CODEWITHSUNDEEP
sqlpostgresqldatetime
Dan Klymenko
Dan Klymenko

Reputation: 21

Iterate over set of dates in PostgreSQL

Currently I'm implementing data base using PostgreSQL. I have the following database(Hope that ER-diagram fully describes it) enter image description here

What do you need to know about this database is that friend can take a rest and have a holiday that is presented in a holiday table by friend's id, start_date and end_date.

I want to create a PostgreSQL query that will find dates, when exactly N friends had a holiday. I'm confused with iterating over period between start and end date.(Every solution that will help with this query is okay).

Example: assume holiday table to have such a content

(1, '2012-05-23', '2012-05-27', 1)
(2, '2012-05-23', '2012-05-24', 2)
(3, '2012-05-24', '2012-05-26', 3)

Assume that friend table contain these three friends.

The output for number of friends = 3 will be '2012-05-24'.

For number of friends = 2 will be ('2012-05-23', '2012-05-25', '2012-05-26').

Upvotes: 0

Views: 583

Answers (1)

Nick
Nick

Reputation: 147196

You can use GENERATE_SERIES to generate a list of all the dates between the earliest start_date and the latest end_date and then LEFT JOIN that to holiday to find all the friends who were on holiday on a given date. You can then count them and filter by the result. For example, to find the days on which 2 friends were on holiday:

SELECT dates.d AS "date", COUNT(*) AS num_on_holiday
FROM GENERATE_SERIES((SELECT MIN(start_date) FROM holiday),
                     (SELECT MAX(end_date) FROM holiday),
                     '1 day'::interval) dates(d)
LEFT JOIN holiday h ON dates.d BETWEEN h.start_date AND h.end_date
GROUP BY dates.d
HAVING COUNT(*) = 2
ORDER BY dates.d

Output:

date                    num_on_holiday
2012-05-23 00:00:00+01  2
2012-05-25 00:00:00+01  2
2012-05-26 00:00:00+01  2

Demo on dbfiddle

Upvotes: 2

Related Questions