CODEWITHSUNDEEP
sqlpostgresqlcalendar
Moamen
Moamen

Reputation: 105

Postgres query for calendar

I am trying to write a query to retrieve data from an events query for a simple calendar app. The table structure is as followed:

table name: events

Column   |  Type      
---------+-----------
 id      | integer
 start   | timestamp
 end     | timestamp

the data inside of the table 

id| start               | end
--+---------------------+--------------------
1 | 2017-09-01 12:00:00 | 2017-09-01 12:00:00
2 | 2017-09-03 10:00:00 | 2017-09-03 12:00:00
3 | 2017-09-08 12:00:00 | 2017-09-11 12:00:00
4 | 2017-09-11 12:00:00 | 2017-09-11 12:00:00

the expected result is

 date      | event.id
-----------+---------
2017-09-01 | 1
2017-09-03 | 2
2017-09-08 | 3
2017-09-09 | 3
2017-09-10 | 3
2017-09-11 | 3
2017-09-11 | 4

As you can see, only days with an event (not just start and end, but also the days in between) is retrieved, days without an event are not retrieved at all. In the second step I would like to be able to limit the amount of distinct days, e.g. "get 4 days with events" what might be more than 4 rows.

Right now I am able to retrieve the events based on start date only using the following query:

SELECT start::date, id FROM events WHERE events.start::date >= '2017-09-01' LIMIT 3

Thinks I already though about are DENSE_RANK and generate_series, but up to now I didn't find a way to fill the gaps between start and end, but not on days where there are no data.

So in short: What I want to get is: get the next X days where there is an event. A date with an event is a day where start <= date >= end

Any ideas ?

Edit

Thanks to Tim I have now the following query (modified to use generate_series instead of a table and added a limit using dense_rank):

select date, id FROM (
SELECT 
    DENSE_RANK() OVER (ORDER BY t1.date) as rank,
    t1.date, 
    events.id 
FROM
    generate_series([DATE]::date, [DATE]::date + interval '365 day', '1 day') as t1
INNER JOIN 
    events 
    ON t1.date BETWEEN events.start::date AND events."end"::date
) as t
WHERE rank <= [LIMIT]

This is working really good, even though I am not 100% sure about the performance hit with this kind of limit

Upvotes: 3

Views: 5219

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522516

I think you really need a calendar table here to cover the full range of dates in which your data may appear. In the first CTE below, I generate a table covering the month of September 2017. Then all we need to do is inner join this calendar table with the events table on the criteria of a given day appearing within a given range.

WITH cte AS (
    SELECT CAST('2017-09-01' AS DATE) + (n || ' day')::INTERVAL AS date
    FROM generate_series(0, 29) n
)

SELECT
    t1.date,
    t2.id
FROM cte t1
INNER JOIN events t2
    ON t1.date BETWEEN CAST(t2.start AS DATE) AND CAST(t2.end AS DATE);

Output:

    date                id
1   01.09.2017 00:00:00 1
2   03.09.2017 00:00:00 2
3   08.09.2017 00:00:00 3
4   09.09.2017 00:00:00 3
5   10.09.2017 00:00:00 3
6   11.09.2017 00:00:00 3
7   11.09.2017 00:00:00 4

Demo here:

Rextester

Upvotes: 3

Related Questions