CODEWITHSUNDEEP
postgresql
gdevaux
gdevaux

Reputation: 2505

PostgreSQL sequence of full weeks number between two dates

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

For example :

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.

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

Upvotes: 0

Views: 374

Answers (1)

chvndb
chvndb

Reputation: 745

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

Upvotes: 1

Related Questions