在Postgres中将间隔间隔为几周 [英] Splitting up an interval in weeks in Postgres

问题描述

这里还有另一个有关日期的SQL问题……

Here goes a yet another SQL question about dates…

我正在使用PHP和Postgres构建日历应用程序，该程序将显示几天，几周甚至几个月的事件。每个事件都有一个开始日期和一个结束日期，按范围选择它们不是问题。但是，如果Postgres可以在每周的第一天拆分多周的活动，这对我很有用。有人告诉我可以使用 GROUP BY 和 EXTRACT 完成此操作，但是我对SQL的理解还不够好

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.

问题是：可以做到这一点，精确的查询是什么样的？目前，我正在使用 SELECT * FROM事件WHERE（范围）OVERLAPS（范围）；然后在PHP中进行拆分，但这显然不是最佳选择。

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.

推荐答案

您可以使用Postgres函数 generate_series 。在8.3及更早版本中，做类似

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);

在8.4中，您也可以这样做

In 8.4, you can also do

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

这篇关于在Postgres中将间隔间隔为几周的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！