在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屋!