title: Postgres query for calendar
body: <p>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:</p>
  
  <pre><code>table name: events
  
  Column   |  Type      
  ---------+-----------
   id      | integer
   start   | timestamp
   end     | timestamp
  </code></pre>
  
  <p>the data inside of the table </p>
  
  <pre><code>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
  </code></pre>
  
  <p>the expected result is</p>
  
  <pre><code> 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
  </code></pre>
  
  <p>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.</p>
  
  <p>Right now I am able to retrieve the events based on start date only using the following query:</p>
  
  <pre><code>SELECT start::date, id FROM events WHERE events.start::date &gt;= '2017-09-01' LIMIT 3
  </code></pre>
  
  <p>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.</p>
  
  <p>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 &lt;= date >= end</p>
  
  <p>Any ideas ?</p>
  
  <h1>Edit</h1>
  
  <p>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):</p>
  
  <pre><code>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 &lt;= [LIMIT]
  </code></pre>
  
  <p>This is working really good, even though I am not 100% sure about the performance hit with this kind of limit</p>