PostgreSQL办公时间

Question

如何将办公时间存储在PostgreSQL行中，以便计算办公时间。

示例：

• 我们星期一到星期五的营业时间是9:00到6:00。
• 星期六上午10点到15点营业。
• 从24岁开始。12月至31日。12月，我们的营业时间为10:00至13:00 (但周六和周日不营业)。
• 像12月25日/26日这样的银行假日是关闭的。
• 以上所有规定的有效期至31日为止。2021年12月
• 从01开始。2022年1月，我们的营业时间是周一到周五10:00到6:00。星期六照常营业。

我想将这些数据存储在行中，这样我们就可以为它开发一个接口。

然后需要使用行/规则来计算特定的开放时间的方法。

在这种情况下，不同的时区并不重要。

我使用PostgreSQL版本12.6。但是如果需要的话，我可以升级到一个更新的版本。

Answer 1

您想要做的是这样的事情(下面的所有代码都可以在这里上找到)：

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
);

我也这么做了：

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)

您可以在您自己的H/W和S/W设置上试验EXPLAIN (ANALYZE, BUFFERS)和性能-请参阅小提琴。

对于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),  -- 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);

然后运行以下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;

为了检查结果，我有一个查询：

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;

这背后的想法是验证SQL正在做我希望它做的事情--所以我检查“边缘”案例--即年初、年底、银行假日前后以及新年前夕的圣诞节期间。

结果集中有55项记录--我只会在新年和圣诞节期间展示这些记录：

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

进一步检查：

SELECT * FROM work_calendar WHERE start_time IS NULL;

返回没有记录-正如我们所期望的！

因此，我们可以看到，我们有适当的时间为适当的日子--即平日，09:00 - 18:00，星期六，10:00至15:00，而周日没有。银行假期和圣诞节期间也提供了服务。显然，您将选择德国/萨克森的银行假日。

Re.性能。我会鼓励你检查你自己的系统的性能--但如果只有一年，那么我不认为这会是一个大问题--但它总是值得记住的良好实践。