PostgreSQL - How to generate range of timestamptz for a specific days in a specific range-CodePudding

I'm creating a telegram bot using Python PostgreSQL to create a somewhat of a registration system for a private clinic and facing a problem of creating doctor's schedule in database. I need to create a range of timestamptz for a specific range of time. I've created a schedule for one day by using next script(I'm using 24h format of time):

INSERT INTO surgeon(appointment_time) 
SELECT t.day
FROM   generate_series(timestamp '2022-11-12 9:00:00'
                     , timestamp '2022-11-12 19:30:00'
                     , interval  '30 minutes') AS t(day)

But how can I create this pattern for every weekday of week(month,year)?

CodePudding user response：

A brute-force approach generates all intermediate timestamps, then filters out those that are out of the regular schedule.

This gives you the slots for the whole month of November (Monday to Friday from 9 AM to 7:30 PM):

insert into surgeon(appointment_time) 
select ts
from generate_series('2022-11-01', '2022-12-01', interval  '30 minutes') as t(ts)
where ts::time between '09:00' and '19:30' 
  and extract(isodow from ts) between 1 and 5