为给定月份生成一系列周间隔 [英] Generate series of week intervals for given month
本文介绍了为给定月份生成一系列周间隔的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
在 Postgres 9.1 数据库中,我试图为给定月份生成一系列周,但有一些限制.我需要所有的星期都从星期一开始,然后在另一个月开始或结束时被削减.
In a Postgres 9.1 database, I am trying to generate a series of weeks for a given month but with some constraints. I need all weeks to start on Monday and get cut when they start or end in another month.
例子:
对于 2013 年 2 月,我想生成一个这样的系列:
For February, 2013 I want to generate a series like this:
start
------------------------
2013-02-01 00:00:00+00
2013-02-04 00:00:00+00
2013-02-11 00:00:00+00
2013-02-18 00:00:00+00
2013-02-25 00:00:00+00
我现在的查询如下所示:
The query that I have now looks like this:
SELECT GREATEST(date_trunc('week', dates.d),
date_trunc('month',dates.d)) as start
FROM generate_series(to_timestamp(1359676800),to_timestamp(1362095999), '1 week') as dates(d)
这个查询让我获得了前 4 周的信息,但它缺少从 25 日开始的那一周.最后一周能拿到吗?
This query gets me the first 4 weeks but it's missing the week from the 25th. Is it possible to get the last week?
推荐答案
select
greatest(date_trunc('week', dates.d), date_trunc('month',dates.d)) as start
from generate_series('2013-02-01'::date, '2013-02-28', '1 day') as dates(d)
group by 1
order by 1
这篇关于为给定月份生成一系列周间隔的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文