PostgreSQL sequence of full weeks number between two dates-CodePudding

I want to get the week number of all the FULL weeks (mon-sun) between two dates.

For example :

start date : 2022-12-01 (it's a thursday)
end date : 2022-12-31 (it's a saturday)

If I wanted the weeks number between theses two dates, I would do :

SELECT extract('week' from dt::date)
FROM generate_series('2022-12-01', '2022-12-31', '1 week'::interval) AS dt

Output : 48 49 50 51 52

But I want to count full weeks only, that has all their 7 days between the two dates.

The first full week starts the first monday after my first date, the 2022-12-05
The last full week ends the last sunday before my last date, the 2022-12-25

What can I do in PostgreSQL to replace 2022-12-01 to 2022-12-05 and 2022-12-31 to 2022-12-25 in the previous code and get only the number of FULL weeks between the two original dates ?

Expected output : 49 50 51

CodePudding user response：

A very simple way of pruning out the partial weeks is to check if the start of a week occurs before the begin date and if the end of a week occurs after the end date.

You can use date_trunc to get the first day of the week and add 6 days to this date to get the end of the week.

select 
  extract('week' from dt::date)
from generate_series('2022-12-01', '2022-12-31', '1 week'::interval) AS dt
where date_trunc('week', dt::date) >= '2022-12-01'::date -- check begin of week occurs before start date
  and date_trunc('week', dt::date)   interval '6 days' <= '2022-12-31'::date -- check end of week occurs before end date