如何使用postgresql从去年获取第一个6个月，即(1-26周)和过去6个月(26-52周)，以及从当前日期开始的最后5周

Question

如何获得去年的前6个月，即(1-26周(和最后6个月(26-52周(&以及如何使用postgresql获取当前日期后的最后5周。

如下表结构

Id Title Description current_week_number current_year
-----------------------------------------------------
123 abc  descr        48                 2021
456 def  descr1       45                 2020

根据周数和年份，我正在尝试获取数据。

有人能帮忙吗？

感谢

Answer 1

查询到目前为止最近6个月的表格：

SELECT *
FROM your_table
WHERE (current_year || '0101') :: date + interval '7 days' * current_week >= Now() - interval '6 months'
AND (current_year || '0101') :: date + interval '7 days' * current_week <= Now()

查询最近12个月和最近6个月之间的表格：

SELECT *
FROM your_table
WHERE (current_year || '0101') :: date + interval '7 days' * current_week <= Now() - interval '6 months'
AND (current_year || '0101') :: date + interval '7 days' * current_week >= Now() - interval '12 months'

查询当前年度前6个月的表格：

SELECT *
FROM your_table
WHERE (current_year || '0101') :: date + interval '7 days' * current_week >= (extract(year from Now()) || '0101') :: date
AND (current_year || '0101') :: date + interval '7 days' * current_week <= (extract(year from Now()) || '0101') :: date + interval '6 months'

查询当前年度最后6个月的表格：

SELECT *
FROM your_table
WHERE (current_year || '0101') :: date + interval '7 days' * current_week >= (extract(year from Now()) || '0101') :: date + interval '6 months'
AND (current_year || '0101') :: date + interval '7 days' * current_week <= (extract(year from Now()) || '0101') :: date + interval '12 months'

Answer 2

--records from 1st half of this year, based on week number
select  Id, Title, Description, current_week_number, current_year
from    your_table
where   current_year=extract('year' from now())::int
and     current_week_number<=26;
--records from 2nd half of this year, based on week number
select  Id, Title, Description, current_week_number, current_year
from    your_table
where   current_year=extract('year' from now())::int
and     current_week_number>26;

关于问题的原始形式，获取实际周数：使用CCD_ 1和CCD_。

with first_26_weeks_of_last_year as 
(   select extract('week' from weeks) weeks
from generate_series(
make_date(  extract('year' from 'today'::timestamp-'1 year'::interval)::int,
1,
1
),
make_date(  extract('year' from 'today'::timestamp-'1 year'::interval)::int,
1,
1
)+'25 weeks'::interval,
'1 week'::interval) weeks),
last_6_months_of_last_year as 
(   select extract('week' from weeks) weeks
from generate_series(
make_date(  extract('year' from 'today'::timestamp-'1 year'::interval)::int,
12,
31
),
make_date(  extract('year' from 'today'::timestamp-'1 year'::interval)::int,
12,
31
)-'6 months'::interval,
'1 week'::interval) weeks),
five_weeks_from_this_week as 
(   select extract('week' from weeks) weeks
from generate_series(
'today'::date,
'today'::date+'4 weeks'::interval,
'1 week'::interval) weeks)
select 'first_26_weeks_of_last_year',a.weeks
from first_26_weeks_of_last_year a
union all
select 'last_6_months_of_last_year',a.weeks
from last_6_months_of_last_year a
union all
select 'five_weeks_from_this_week',a.weeks
from five_weeks_from_this_week a;