SQLize | PHPize | SQLtest

select (case when extract('month' from (date_trunc('week', dates))) != extract('month' from dates) then (date_trunc('month', dates)) when extract('year' from (date_trunc('week', dates))) != extract('year' from dates) then (date_trunc('year', dates)) else date_trunc('week', dates) end)::date as start_week, (case when extract('month' from (date_trunc('week', dates) + '6 days')) != extract('month' from dates) then (date_trunc('month', dates) + '1 month - 1 day') when extract('year' from (date_trunc('week', dates) + '6 days')) != extract('year' from dates) then (date_trunc('month', dates) + '1 year - 1 day') else (date_trunc('week', dates) + '6 days') end)::date as end_week from generate_series('2023-01-01', '2023-12-31', interval '1 day') as dates group by start_week, end_week order by start_week;