Return to Answer

Following comments by the OP, here's some code to set up some sort of shift scheduling. It could probably do with some treatment with PL/pgSQL to tidy it up, for for recurring schedules, as a baseline, it might be of help.

SELECT
  d.i,
  h.i,
 '2022-10-31 00:00:00'::TIMESTAMP + d.i * '1 DAY'::INTERVAL + h.i * '1 HOUR'::INTERVAL AS "Slot",
  EXTRACT(DOW FROM  '2022-10-31 00:00:00'::TIMESTAMP + d.i * '1 DAY'::INTERVAL + h.i * '1 HOUR'::INTERVAL) AS "Day num",
  TO_CHAR( '2022-10-31 00:00:00'::TIMESTAMP + d.i * '1 DAY'::INTERVAL + h.i * '1 HOUR'::INTERVAL, 'DAY') AS "Day name",
  CASE
    WHEN 
      EXTRACT(DOW  FROM '2022-10-31 00:00:00'::TIMESTAMP + d.i * '1 DAY'::INTERVAL + h.i * '1 HOUR'::INTERVAL) =   1 AND 
      EXTRACT(HOUR FROM '2022-10-31 00:00:00'::TIMESTAMP + d.i * '1 DAY'::INTERVAL + h.i * '1 HOUR'::INTERVAL) >=  9 AND
      EXTRACT(HOUR FROM '2022-10-31 00:00:00'::TIMESTAMP + d.i * '1 DAY'::INTERVAL + h.i * '1 HOUR'::INTERVAL) <= 17 THEN TRUE
    WHEN 
      EXTRACT(DOW  FROM '2022-10-31 00:00:00'::TIMESTAMP + d.i * '1 DAY'::INTERVAL + h.i * '1 HOUR'::INTERVAL) =   2 AND 
      EXTRACT(HOUR FROM '2022-10-31 00:00:00'::TIMESTAMP + d.i * '1 DAY'::INTERVAL + h.i * '1 HOUR'::INTERVAL) >= 10 AND
      EXTRACT(HOUR FROM '2022-10-31 00:00:00'::TIMESTAMP + d.i * '1 DAY'::INTERVAL + h.i * '1 HOUR'::INTERVAL) <= 18 THEN TRUE
    ELSE FALSE
  END AS "Shift"

 
FROM 
  GENERATE_SERIES(0,  6) AS d(i),  --  <<-- generates hourly slots for weeks/months... on end
  GENERATE_SERIES(0, 23) AS h(i)

  LIMIT 150;                        -- don't want to overload db<>fiddle - this shows the example shifts.

Result:

So, you can see here (and if you examine the fiddle) that Mondays from 09:00 to 17:00 are shift hours and then Tuesdays from 10:00 to 18:00 are shift hours. I'm not sure that any more help can be forthcoming!

Following comments by the OP, here's some code to set up some sort of shift scheduling. It could probably do with some treatment with PL/pgSQL to tidy it up, for recurring schedules, as a baseline, it might be of help.

SELECT
  d.i,
  h.i,
 '2022-10-31 00:00:00'::TIMESTAMP + d.i * '1 DAY'::INTERVAL + h.i * '1 HOUR'::INTERVAL AS "Slot",
  EXTRACT(DOW FROM  '2022-10-31 00:00:00'::TIMESTAMP + d.i * '1 DAY'::INTERVAL + h.i * '1 HOUR'::INTERVAL) AS "Day num",
  TO_CHAR( '2022-10-31 00:00:00'::TIMESTAMP + d.i * '1 DAY'::INTERVAL + h.i * '1 HOUR'::INTERVAL, 'DAY') AS "Day name",
  CASE
    WHEN 
      EXTRACT(DOW  FROM '2022-10-31 00:00:00'::TIMESTAMP + d.i * '1 DAY'::INTERVAL + h.i * '1 HOUR'::INTERVAL) =   1 AND 
      EXTRACT(HOUR FROM '2022-10-31 00:00:00'::TIMESTAMP + d.i * '1 DAY'::INTERVAL + h.i * '1 HOUR'::INTERVAL) >=  9 AND
      EXTRACT(HOUR FROM '2022-10-31 00:00:00'::TIMESTAMP + d.i * '1 DAY'::INTERVAL + h.i * '1 HOUR'::INTERVAL) <= 17 THEN TRUE
    WHEN 
      EXTRACT(DOW  FROM '2022-10-31 00:00:00'::TIMESTAMP + d.i * '1 DAY'::INTERVAL + h.i * '1 HOUR'::INTERVAL) =   2 AND 
      EXTRACT(HOUR FROM '2022-10-31 00:00:00'::TIMESTAMP + d.i * '1 DAY'::INTERVAL + h.i * '1 HOUR'::INTERVAL) >= 10 AND
      EXTRACT(HOUR FROM '2022-10-31 00:00:00'::TIMESTAMP + d.i * '1 DAY'::INTERVAL + h.i * '1 HOUR'::INTERVAL) <= 18 THEN TRUE
    ELSE FALSE
  END AS "Shift"
FROM 
  GENERATE_SERIES(0,  6) AS d(i),  --  <<-- generates hourly slots for weeks/months... on end
  GENERATE_SERIES(0, 23) AS h(i)

LIMIT 150;                        -- don't want to overload db<>fiddle - this shows the example shifts.

Result (snipped for brevity):

So, you can see here (and if you examine the fiddle) that Mondays from 09:00 to 17:00 are shift hours and then Tuesdays from 10:00 to 18:00 are shift hours.

Following comments by the OP, here's some code to set up some sort of shift scheduling. It could probably do with some treatment with PL/pgSQL to tidy it up, for for recurring schedules, as a baseline, it might be of help.

Following comments by the OP, here's some code to set up some sort of shift scheduling. It could probably do with some treatment with PL/pgSQL to tidy it up, for for recurring schedules, as a baseline, it might be of help.

Following comments by the OP, here's some code to set up some sort of shift scheduling. It could probably do with some treatment with PL/pgSQL to tidy it up, for for recurring schedules, as a baseline, it might be of help.

Your comment says (presumably as an example) "Mondays 9AM to 5PM, Tuesdays 10AM to 6PM, .., so I"ve catered for that example - you can obviously add code of your own for more complex scenarios. You could also make use of the range data type as outlined above.

SELECT
  d.i,
  h.i,
 '2022-10-31 00:00:00'::TIMESTAMP + d.i * '1 DAY'::INTERVAL + h.i * '1 HOUR'::INTERVAL AS "Slot",
  EXTRACT(DOW FROM  '2022-10-31 00:00:00'::TIMESTAMP + d.i * '1 DAY'::INTERVAL + h.i * '1 HOUR'::INTERVAL) AS "Day num",
  TO_CHAR( '2022-10-31 00:00:00'::TIMESTAMP + d.i * '1 DAY'::INTERVAL + h.i * '1 HOUR'::INTERVAL, 'DAY') AS "Day name",
  CASE
    WHEN 
      EXTRACT(DOW  FROM '2022-10-31 00:00:00'::TIMESTAMP + d.i * '1 DAY'::INTERVAL + h.i * '1 HOUR'::INTERVAL) =   1 AND 
      EXTRACT(HOUR FROM '2022-10-31 00:00:00'::TIMESTAMP + d.i * '1 DAY'::INTERVAL + h.i * '1 HOUR'::INTERVAL) >=  9 AND
      EXTRACT(HOUR FROM '2022-10-31 00:00:00'::TIMESTAMP + d.i * '1 DAY'::INTERVAL + h.i * '1 HOUR'::INTERVAL) <= 17 THEN TRUE
    WHEN 
      EXTRACT(DOW  FROM '2022-10-31 00:00:00'::TIMESTAMP + d.i * '1 DAY'::INTERVAL + h.i * '1 HOUR'::INTERVAL) =   2 AND 
      EXTRACT(HOUR FROM '2022-10-31 00:00:00'::TIMESTAMP + d.i * '1 DAY'::INTERVAL + h.i * '1 HOUR'::INTERVAL) >= 10 AND
      EXTRACT(HOUR FROM '2022-10-31 00:00:00'::TIMESTAMP + d.i * '1 DAY'::INTERVAL + h.i * '1 HOUR'::INTERVAL) <= 18 THEN TRUE
    ELSE FALSE
  END AS "Shift"


FROM 
  GENERATE_SERIES(0,  6) AS d(i),  --  <<-- generates hourly slots for weeks/months... on end
  GENERATE_SERIES(0, 23) AS h(i)

  LIMIT 150;                        -- don't want to overload db<>fiddle - this shows the example shifts.

Result:

i   i   Slot    Day num     Day name    Shift
0   0   2022-10-31 00:00:00     1   MONDAY  f
0   1   2022-10-31 01:00:00     1   MONDAY  f
0   2   2022-10-31 02:00:00     1   MONDAY  f
0   3   2022-10-31 03:00:00     1   MONDAY  f
0   4   2022-10-31 04:00:00     1   MONDAY  f
0   5   2022-10-31 05:00:00     1   MONDAY  f
0   6   2022-10-31 06:00:00     1   MONDAY  f
0   7   2022-10-31 07:00:00     1   MONDAY  f
0   8   2022-10-31 08:00:00     1   MONDAY  f
0   9   2022-10-31 09:00:00     1   MONDAY  t
0   10  2022-10-31 10:00:00     1   MONDAY  t
0   11  2022-10-31 11:00:00     1   MONDAY  t
0   12  2022-10-31 12:00:00     1   MONDAY  t

So, you can see here (and if you examine the fiddle) that Mondays from 09:00 to 17:00 are shift hours and then Tuesdays from 10:00 to 18:00 are shift hours. I'm not sure that any more help can be forthcoming!