Separation of intervals in weeks in Postgres

Question

Here is another SQL question about dates ...

I am creating a calendar application using PHP and Postgres that will 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 helpful if Postgres can share multi-week events on the first day of each week. I was told that this can be done with GROUP BYand EXTRACT, but I'm not good enough in SQL to figure out how to do this.

Question: can this be done, and what will be the exact request? I am currently using SELECT * FROM events WHERE (range) OVERLAPS (range); and then do splitting in PHP, but this is clearly not optimal.

Answer 1

You can use the Postgres generate_series function . In 8.3 and earlier, do something 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');

Answer 2

First of all - it would be nice to show an example of input and output of the request that you want to help us with - for example, I really do not understand what you need.

In addition, you may find that OVERLAPS is suboptimal, as it is not indexed. Instead, you can use the method described in this blog post .