0
select week_num, week_start, week_end,to_char(week_start,'dd Dy Mon yyyy'), to_char(week_end,'dd Dy Mon yyyy') from(
WITH RECURSIVE t(n) AS (
    select (date_trunc('week',(date_trunc('week',(2016 || '-01-04')::date)::date - interval '1 day')::date))::date
  UNION ALL
    SELECT (n - interval '1 week')::date  FROM t WHERE  extract(WEEK from n ) > 1
)
SELECT n as week_start, (n + interval '6 days')::date as week_end, extract(WEEK from n ) as week_num  
FROM t 
) as weeks order by week_num

i wrote this postgresl script to get the first and last day of all iso week in a given year. It is working perfectly, i just need to know if it can be improved

Ihor Romanchenko
  • 26,995
  • 8
  • 48
  • 44
user3010646
  • 1
  • 1

1 Answers1

5

You can use generate_series() to avoid the convoluted CTE and date arithmetics. Here's an example to get you started:

select d, d + interval '6 days'
from generate_series('2016-01-01'::date, '2016-12-31'::date, '1 day'::interval) d
where date_trunc('week', d) = d

You'll want to add a case in the second term to strip out anything in 2017, and it could be rewritten to step a week at a time, but it should get you on the right track.

Denis de Bernardy
  • 75,850
  • 13
  • 131
  • 154