Postgresql – Office Hours in PostgreSQL

date mathdatetimepostgresql

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.

Best Answer

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.

Related Question