How to get the 1st 6months i.e.(1-26weeks) and last 6months (26-52 weeks) from last year & also get-CodePudding

How to get the 1st 6months i.e.(1-26weeks) and last 6months (26-52 weeks) from last year & also how to get last 5weeks from current date using postgresql.

Like the below table structure

Id Title Description current_week_number current_year
-----------------------------------------------------

123 abc  descr        48                 2021
456 def  descr1       45                 2020

Based on the week number and year I'm trying to fetch the data.

can anyone help on this?

Thanks

CodePudding user response：

--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;

As to the original form of the question, getting the actual weeks: using generate_series(date,date,interval) and extract('field' from date).

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;

CodePudding user response：

Query the table on the last 6 months until now :

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()

Query the table between the last 12 months and the last 6 months from now :

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'

Query the table for the first 6 months of the current year :

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'

Query the table for the last 6 months of the current year :

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'