提问人：dipesh patidar 提问时间：10/11/2022 最后编辑：Raed Alidipesh patidar 更新时间：10/12/2022 访问量：24
如何在postgresql中生成一个每周系列，该系列的开始日期应为月份开始日期，结束日期应为开始日期+ 6
How can I generate a weekly series which should have start date as month start date and end date should be start date + 6 in postgresql
问：
以下是我尝试过的查询：
with weeks as (
select d::date start_date, case when to_char(d::date+6,'Month') =
to_char(d::date,'Month') then d::date+ 6 else (date_trunc('month',
to_timestamp(to_char(d::date,'YYYY-MM-DD') ,'YYYY-MM-DD HH:MI:SS')) + interval '1
month - 1 day')::date end as end_date
from generate_series('2022-01-01', '2022-07-30', '7d'::interval) d
)
我希望结果如下：
| 开始日期 | 结束日期 |
|---|---|
| '2022-01-01' | '2022-01-07 |
| '2022-01-08' | '2022-01-14 |
它应该从 2022-01-29 到 2022-01-31，然后下个月它应该从“2022-02-01”到“2022-02-07”，并且应该继续。
答：
1赞
user330315
10/11/2022
#1
我会结合到这里。一个用于月初，一个用于一周的开始。generate_series()
可以通过计算当月的最后一天来使用函数来评估周末：least()
select gw.week_start::date start_date,
least(gw.week_start::date + 6, date_trunc('month', gw.week_start) + interval '1 month - 1 day')::date as end_date
from generate_series('2022-01-01', '2022-07-30', interval '1 month') as gm(month_start)
cross join generate_series(gm.month_start, gm.month_start + interval '1 month - 1 day', interval '1 week') as gw(week_start);
这将返回：
start_date | end_date
-----------+-----------
2022-01-01 | 2022-01-07
2022-01-08 | 2022-01-14
2022-01-15 | 2022-01-21
2022-01-22 | 2022-01-28
2022-01-29 | 2022-01-31
2022-02-01 | 2022-02-07
2022-02-08 | 2022-02-14
2022-02-15 | 2022-02-21
2022-02-22 | 2022-02-28
2022-03-01 | 2022-03-07
2022-03-08 | 2022-03-14
2022-03-15 | 2022-03-21
2022-03-22 | 2022-03-28
2022-03-29 | 2022-03-31
2022-04-01 | 2022-04-07
2022-04-08 | 2022-04-14
2022-04-15 | 2022-04-21
2022-04-22 | 2022-04-28
2022-04-29 | 2022-04-30
2022-05-01 | 2022-05-07
2022-05-08 | 2022-05-14
2022-05-15 | 2022-05-21
2022-05-22 | 2022-05-28
2022-05-29 | 2022-05-31
2022-06-01 | 2022-06-07
2022-06-08 | 2022-06-14
2022-06-15 | 2022-06-21
2022-06-22 | 2022-06-28
2022-06-29 | 2022-06-30
2022-07-01 | 2022-07-07
2022-07-08 | 2022-07-14
2022-07-15 | 2022-07-21
2022-07-22 | 2022-07-28
2022-07-29 | 2022-07-31
评论