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(0) NULL,
end_time TIME(0) NULL
);
I also did this:
CREATE INDEX st_ix ON work_calendar (start_time); -- these indexes reduce the execution time
CREATE INDEX et_ix ON work_calendar (end_time); -- run EXPLAIN (ANALYZE, BUFFERS)
You can experiment with EXPLAIN (ANALYZE, BUFFERS) and performance on your own H/W and S/W setup - see the fiddle.
and for bank_holidays - some of which are variable (Easter for example):
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), -- Irish feast day
('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 run the following SQL:
-- EXPLAIN (ANALYZE, BUFFERS) -- check with different indexing strategies.
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
-- Set start time = '00:00:00' for bank holidays and Sundays
WHEN (opening_day IN (SELECT bh_date FROM bank_holiday))
OR EXTRACT(DOW FROM opening_day) = 0 THEN '00:00:00'::TIME
-- Ater this WHEN, the CASE statement is over - it's like BREAK; in C (or JAVA...)
-- the code drops out of the CASE statement.
-- So, now, we tackle Saturdays and the Christmas period dates:
-- set start time = 10:00:00 for Saturdays that are not bank holidays and
-- set start time = 10:00:00 for days from Christmas Eve to New Year's Day
WHEN EXTRACT(DOW FROM opening_day) = 6
OR (opening_day >= '2021-12-24' AND opening_day <= '2021-12-31') THEN '10:00:00'::TIME
-- Now, we deal with the rest - i.e. Mondays to Fridays of those days which are
-- not Bank Holidays or in the Christmas period.
WHEN EXTRACT (DOW FROM opening_day) BETWEEN 1 AND 5 THEN '09:00'::TIME
ELSE NULL
END AS ot,
CASE
-- Set end time = '00:00:00' for Bank Holidays and Sundays
WHEN (opening_day IN (SELECT bh_date FROM bank_holiday))
OR EXTRACT(DOW FROM opening_day) = 0 THEN '00:00:00'::TIME
-- Ater this WHEN, the CASE statement is over - it's like BREAK; in C (or JAVA...)
-- the code drops out of the CASE statement.
-- So, now, we tackle the Christmas period dates: - the Christmas period end time
-- is 13:00 and not 15:00 - i.e. it's not (unlike for start time) the normal
-- Saturday end time - so we need an extra WHEN in the CASE
-- set end time = 13:00:00 for Saturdays that are not bank holidays and
-- set start time = 10:00:00 for days from Christmas Eve to New Year's Day
WHEN opening_day >= '2021-12-24' AND opening_day <= '2021-12-31'
THEN '13:00:00'::TIME
-- Now, we deal with normal Saturdays which are not Bank Holidays or which
-- don't fall in the Christmas period.
WHEN EXTRACT(DOW FROM opening_day) = 6 THEN '15:00:00'::TIME
-- Finally, we have the normal working day - end time is 18:00
WHEN EXTRACT (DOW FROM opening_day) BETWEEN 1 AND 5 THEN '18:00'::TIME
ELSE NULL
END AS ft
FROM t;
And to check the result, I had this query:
SELECT * FROM work_calendar
WHERE the_day >= '2021-01-01' AND the_day <= '2021-01-13'
OR the_day >= '2021-03-13' AND the_day <= '2021-03-24' -- St. Patrick's day Bank Holiday
OR the_day >= '2021-04-01' AND the_day <= '2021-04-10' -- Easter Monday
OR the_day >= '2021-04-28' AND the_day <= '2021-05-05' -- May Day
OR the_day >= '2021-12-20' AND the_day <= '2021-12-31' -- Christmas period
ORDER BY the_day;
The idea behind this is to verify that the SQL is doing what I hope it's doing - so I check the "edge" cases - i.e. beginning of year, end of year, around Bank Holidays and over the Christmas period to New Year's Eve.
There are 55 records in the result set - I'll only show those around the New Year and the Christmas period:
the_day day_name start_time end_time
2021-01-01 Friday 00:00:00 00:00:00 -- BH - no work
2021-01-02 Saturday 10:00:00 15:00:00 -- Sat. st 10:00, et 15:00
2021-01-03 Sunday 00:00:00 00:00:00 -- Sun. day off - so far, so good
2021-01-04 Monday 09:00:00 18:00:00 -- Normal work resumes
2021-01-05 Tuesday 09:00:00 18:00:00 -- "
...
... gap -- inspection shows that these are all OK
...
2021-12-20 Monday 09:00:00 18:00:00 -- Normal working day
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 -- Christmas Eve - start of Christmas period 10 - 13
2021-12-25 Saturday 00:00:00 00:00:00 -- Christmas Day - BH, no work!
2021-12-26 Sunday 00:00:00 00:00:00 -- Sunday + St. Stephen's day - no work
2021-12-27 Monday 10:00:00 13:00:00 -- Christmas period working
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 -- "
55 rows
A further check:
SELECT * FROM work_calendar WHERE start_time IS NULL;
returns no records - as we would expect!
So, we can see that we have the appropriate hours for the appropriate days - i.e. weekday, 09:00 - 18:00, Saturday, 10:00 to 15:00 and nothing on Sunday. Bank Holidays and the Christmas period are also catered for. Obviously, you will choose Bank Holidays for Germany/Saxony.
Re. performance. I would encourage you to check your own system for performance - but if it's only a year, then I don't imagine that it's going to be a huge problem - but it's always worth bearing in mind as good practice.
