How to get data for administrative weeks of a month?

What I want to do is to get the data of each “administrative” week (Monday to Sunday), that is if a month starts a Wednesday,the next week when I would launch the query, I would want the data from Wednesday, Thursday, Friday, Saturday, Sunday of the past week. This would get me data of week 1, and so forth for each week of the current month.

I made the following query:

with connection.cursor() as cursor:
    sql = 'SELECT COUNT(*) ' 
          'FROM panneau ' 
          'WHERE year(datecreation) = year(now()) ' 
          'AND month(datecreation) = month(now()) ' 
          'AND datecreation [I don't know what to put here] '
    cursor.execute(sql)
    test = cursor.fetchall()
    print(test)

But I don’t know what to put in the last line.

In the end I should have 4 to 5 variables with the data of each “administrative week”, which would take into account when the first week of the month starts and when the last week of the month ends.

Edit : for example with the month on July 2022

• Week 1 = Friday 1st to Sunday 3rd.
• Week 2 = the 4th to 10th.
• Week 3 = 11th to 17th
• Week 4 = 18th 24th
• Week 5 = 25th to 31st

Some help would be helpful please, thank you.

Answer

To get the previous administrative week, you can check if a potential date exists in a range of prior week dates, offset by the current datetime’s dow, generated by generate_series:

select p.datecreation from panneau p
where date(p.datecreation) in 
  (select date(p1.dt) from 
      (select now() - (extract(dow from now())::text || ' days')::interval - ((7-v)::text|| ' days')::interval dt 
       from generate_series(1,7) v) p1 
   where extract(month from p1.dt) = extract(month from now() - (extract(dow from now())::text || ' days')::interval))

See fiddle.