1

How to store office hours in PostgreSQL rows, so that I can calculate the office hours.

Example:

  • We have open from 9:00 to 18:00 from Monday till Friday.
  • Saturday we have open from 10:00 to 15:00
  • from 24. Dec to 31. Dec we have open from 10:00 to 13:00 (but not on Saturday and Sunday)
  • bank holidays like 25/26 December are closed.
  • All above rules are valid until 31. Dec 2021.
  • Starting from 01. January 2022 we have open from 10:00 to 18:00 from Monday till Friday. Saturday is open like before.

I would like to store this data in rows, so that we can develop an interface for it.

And then there needs to be a method which uses the rows/rules to calculate the specific opening hours.

Different timezone are not important in this context.

I use PostgreSQL version 12.6. But if needed I can upgrade to a newer version.

0
5

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.

2
  • Thank you very much for your answer. I will take your idea that there are tables which contain the source data (which can be edited by users via a GUI), and there is a table which contains the generated entries. I will add a column "source" to the table holding the generated data, so that I update/delete these entries if the source data gets changed. – guettli May 4 at 10:46
  • You will have to make bank_holiday editable for variable holidays - Easter for example. Here in Ireland, we normally have the "May Day" holiday on the following Monday - i.e. also variable). You could have the t table (should maybe have called it calendar or days_of_year?) on disk - even 25 years of data wouldn't be very much. If you found my answer helpful and/or correct, please mark it as such - not so much for points - I have plenty of those now - but upvoted answers tend to be found by searches. I upvoted yours, not looking for a quid pro quo, but 'cos I learnt stuff from it! :-) – Vérace May 4 at 11:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.