Splitting up an interval in weeks in Postgres

date, postgresql, range, split, sql

Solution

You can use the Postgres function generate_series. In 8.3 and earlier, do things like

select current_date + s.a as dates from generate_series(0,14,7) as s(a);

In 8.4, you can also do

SELECT * FROM generate_series('2008-03-01 00:00'::timestamp,
                              '2008-03-04 12:00', '10 hours');

Problem

Here goes a yet another SQL question about dates… I'm building a calendaring application using PHP and Postgres that would display events spanning days, weeks or possibly months. Each event has a start date and an end date, and selecting them by range is not a problem; however, it would be useful for me if Postgres could split multi-week events at the first day of each week. I've been told it can be done using `GROUP BY` and `EXTRACT`, but I'm not good enough with SQL to understand how. The question is: can this be done, and what would the precise query look like? Currently I'm using `SELECT * FROM events WHERE (range) OVERLAPS (range)`; and then doing the splitting in PHP, but it's clearly not optimal.

Original source