datetime – Office Hours in PostgreSQL

What you want to do is something like this (all the code below is available on the fiddle here):

CREATE TABLE work_calendar
(
  the_day    DATE NOT NULL PRIMARY KEY,
  day_name   TEXT NOT NULL,
  start_time TIME NOT NULL,
  end_time   TIME NOT NULL
);

and for bank_holidays:

CREATE TABLE bank_holiday
(
  the_day TEXT NOT NULL,
  bh_date DATE NOT NULL
);

INSERT INTO bank_holiday 
VALUES
('New Year''s Day',    '2021-01-01'::DATE),
('St. Patrick''s Day', '2021-03-17'::DATE),
('Easter Monday',      '2021-05-04'::DATE),
('May Day',            '2021-05-01'::DATE),
('Christmas Day',      '2021-12-25'::DATE),
('St. Stephen''s Day', '2021-12-26'::DATE);

And then you can do the following:

BEGIN TRANSACTION;   -- discussed below!
WITH t (opening_day) AS
(
  SELECT  GENERATE_SERIES
  (
    '2021-01-01'::DATE,
    '2021-12-31'::DATE,
    '1 DAY'
  ) AS ds
)
INSERT INTO work_calendar
SELECT 
  opening_day,
  TO_CHAR(opening_day, 'Day'),
      
  CASE 
    WHEN EXTRACT(DOW FROM opening_day) IN (1, 2, 3, 4, 5) THEN '09:00:00'::TIME
    WHEN EXTRACT(DOW FROM opening_day) = 6                THEN '10:00:00'::TIME
    ELSE                                                       '00:00:00'::TIME
  END AS ot,
    
  CASE 
    WHEN EXTRACT(DOW FROM opening_day) IN (1, 2, 3, 4, 5) THEN '18:00:00'::TIME
    WHEN EXTRACT(DOW FROM opening_day) = 6                THEN '15:00:00'::TIME
    ELSE                                                       '00:00:00'::TIME
  END AS ct
FROM t;
COMMIT; -- discussed below.

And to check:

SELECT * FROM work_calendar ORDER BY the_day;

Result:

the_day day_name    start_time  end_time
2021-01-01  Friday      09:00:00    18:00:00
2021-01-02  Saturday    10:00:00    15:00:00
2021-01-03  Sunday      00:00:00    00:00:00
2021-01-04  Monday      09:00:00    18:00:00

So, we can see that we have the appropriate hours for the given day – i.e. weekday, 09:00 – 18:00, Saturday, 10:00 to 15:00 and nothing on Sunday.

Now, we haven’t taken the Christmas period (10:00 – 13:00) into account:

UPDATE work_calendar
SET start_time = '10:00:00', end_time = '13:00:00' 
WHERE (the_day >= '2021-12-24' AND the_day <= '2021-12-31') 
AND EXTRACT(DOW FROM the_day) NOT IN (0, 6);

And also, we haven’t taken bank holidays into account, so we do the following:

UPDATE work_calendar 
SET start_time = '00:00:00'::TIME, end_time = '00:00:00'
FROM bank_holiday
WHERE work_calendar.the_day = bank_holiday.bh_date;

And to check:

SELECT * FROM work_calendar ORDER BY the_day;

Result:

the_day day_name    start_time  end_time
2021-01-01  Friday      00:00:00    00:00:00   <<-- Bank Holiday
2021-01-02  Saturday    10:00:00    15:00:00
2021-01-03  Sunday      00:00:00    00:00:00
2021-01-04  Monday      09:00:00    18:00:00
2021-01-05  Tuesday     09:00:00    18:00:00

So, now we see that the New Year’s day bank holiday has been changed from above to nothing for either start_time or end_time as you would expect for a bank holiday. Obviously, you can choose bank holidays which will suit your own particular country/locale.

We can also check the Christmas period:

SELECT * FROM work_calendar WHERE the_day >= '2021-12-21'::DATE;

Result:

the_day day_name    start_time  end_time
2021-12-21  Tuesday     09:00:00    18:00:00
2021-12-22  Wednesday   09:00:00    18:00:00
2021-12-23  Thursday    09:00:00    18:00:00
2021-12-24  Friday      10:00:00    13:00:00  <<--- now, from finish at 13:00
2021-12-25  Saturday    00:00:00    00:00:00  <<--- not a work day - as expected!
2021-12-26  Sunday      00:00:00    00:00:00
2021-12-27  Monday      10:00:00    13:00:00
2021-12-28  Tuesday     10:00:00    13:00:00
2021-12-29  Wednesday   10:00:00    13:00:00
2021-12-30  Thursday    10:00:00    13:00:00
2021-12-31  Friday      10:00:00    13:00:00

So, the 10:00 to 13:00 times are correct – and Christmas Day (Sat.) is not worked.

You can run all of the above in one transaction to ensure ACID features – however, since it’s a one-off, that’s probably not a priority in this case? You can put the UPDATE before the end of the transaction with the BEGIN TRANSACTION; and the COMMIT;.

It could be done in one pass – but in this case, as it’s a one-off, it’s probably not worthwhile – but I can provide the code if necessary – let me know!