统计中经常会遇到,近一周,近一月,近三月,近一年数据统计,下面提供了postgresql拆分日期段的sql
-- 近一周,每一天起止时间
select generate_series startDate,generate_series + interval '1 day' endDate
from generate_series( NOW()::date + interval ' -6 day', now()::timestamp , '1day');
-- 近一月 ,每一天起止时间
select generate_series startDate,generate_series + interval '1 day' endDate
from generate_series( NOW()::date + interval ' -1 month', now()::timestamp , '1day');
-- 近三个月,每一周起止时间
select
date_trunc('week', generate_series) startDate,
date_trunc('week', generate_series + interval ' 1 week') endDate
from generate_series( NOW()::timestamp + interval ' - 12 week', now()::timestamp , '1week');
-- 近一年,每月起止时间
select date_trunc('month', generate_series) startDate,
date_trunc('month', generate_series + interval ' 1 month' ) endDate
from generate_series(NOW()::date + interval ' -11 month', now()::timestamp , '1month');
-- 近一周,每一天起止时间 测试时间为2023-10-09
![](https://img-blog.csdnimg.cn/bf5b3b2946d04926a5aaf9f83584badc.png)
-- 近一月 ,每一天起止时间 测试时间为2023-10-09
![](https://img-blog.csdnimg.cn/695ba6af66004f5b88fa357853985e20.png)
-- 近三个月,每一周起止时间,测试时间为2023-10-09
![](https://img-blog.csdnimg.cn/b33942f875eb49ddb8112911978d5e58.png)
-- 近一年,每月起止时间 测试时间为2023-10-09
![](https://img-blog.csdnimg.cn/fb1077cc19ff409494ad13a554d217b0.png)