sql - 计算PostgreSQL中2个日期之间的工作时间

Question

我正在使用 Postgres (PL/pgSQL) 开发算法，我需要计算 2 个时间戳之间的工作小时数，考虑到周末不工作，其余时间仅从早上 8 点到下午 15 点计算.

例子:

• 从 12 月 3 日下午 14 点到 12 月 4 日上午 9 点应计为 2 小时:

  3rd = 1, 4th = 1
  

• 从 12 月 3 日下午 15 点到 12 月 7 日早上 8 点应计为 8 小时:

  3rd = 0, 4th = 8, 5th = 0, 6th = 0, 7th = 0
  

最好也考虑小时分数。

Answer 1

根据您的问题，工作时间是:周一至周五，08:00–15:00 .

四舍五入的结果

对于两个给定的时间戳

以1 小时为单位 运行。分数被忽略，因此不是精确而是简单:

SELECT count(*) AS work_hours
FROM   generate_series (timestamp '2013-06-24 13:30'
                      , timestamp '2013-06-24 15:29' <b>- interval '1h'</b>
                      , interval '1h') h
WHERE  EXTRACT(ISODOW FROM h) < 6
AND    h::time >= '08:00'
AND    h::time <b><= '14:00'</b>;

• 函数generate_series()如果结束大于开始，则生成一行，并为每个 完整 给定时间间隔(1 小时)生成另一行。此世界计数 输入的每个小时。要忽略小数小时，请从末尾减去 1 小时。并且不要计算 14:00 之前开始的时间。

• 为EXTRACT() 使用字段模式ISODOW 而不是DOW来简化表达式。对于星期日，返回 7 而不是 0。

• time 的简单(且非常便宜)转换可以轻松识别符合条件的时间。

• 小时的小数部分将被忽略，即使间隔开始和结束的小数部分加起来等于或更长。

对于整个表

CREATE TABLE t (t_id int PRIMARY KEY, t_start timestamp, t_end timestamp);
INSERT INTO t VALUES 
  (1, '2009-12-03 14:00', '2009-12-04 09:00')
, (2, '2009-12-03 15:00', '2009-12-07 08:00')  -- examples in question
, (3, '2013-06-24 07:00', '2013-06-24 12:00')
, (4, '2013-06-24 12:00', '2013-06-24 23:00')
, (5, '2013-06-23 13:00', '2013-06-25 11:00')
, (6, '2013-06-23 14:01', '2013-06-24 08:59')  -- max. fractions at begin and end
;

查询:

SELECT t_id, count(*) AS work_hours
FROM  (
   SELECT t_id, generate_series (t_start, t_end - interval '1h', interval '1h') AS h
   FROM   t
   ) sub
WHERE  EXTRACT(ISODOW FROM h) < 6
AND    h::time >= '08:00'
AND    h::time <= '14:00'
GROUP  BY 1
ORDER  BY 1;

db<> fiddle here
<子>旧sqlfiddle

更精确

要获得更高的精度，您可以使用更小的时间单位。例如 5 分钟片段:

SELECT t_id, count(*) * interval '5 min' AS work_interval
FROM  (
   SELECT t_id, generate_series (t_start, t_end - interval '5 min', interval '5 min') AS h
   FROM   t
   ) sub
WHERE  EXTRACT(ISODOW FROM h) < 6
AND    h::time >= '08:00'
AND    h::time <= '14:55'  -- 15.00 - interval '5 min'
GROUP  BY 1
ORDER  BY 1;

单位越小，成本越高。

在 Postgres 9.3+ 中使用 LATERAL 进行清理

结合新的LATERAL Postgres 9.3 中的特性，上面的查询可以写成:

1 小时精度:

SELECT t.t_id, h.work_hours
FROM   t
LEFT   JOIN LATERAL (
   SELECT count(*) AS work_hours
   FROM   generate_series (t.t_start, t.t_end - interval '1h', interval '1h') h
   WHERE  EXTRACT(ISODOW FROM h) < 6
   AND    h::time >= '08:00'
   AND    h::time <= '14:00'
   ) h ON TRUE
ORDER  BY 1;

5 分钟精度:

SELECT t.t_id, h.work_interval
FROM   t
LEFT   JOIN LATERAL (
   SELECT count(*) * interval '5 min' AS work_interval
   FROM   generate_series (t.t_start, t.t_end - interval '5 min', interval '5 min') h
   WHERE  EXTRACT(ISODOW FROM h) < 6
   AND    h::time >= '08:00'
   AND    h::time <= '14:55'
   ) h ON TRUE
ORDER  BY 1;

这具有额外优势，即包含零工作时间的间隔不会像上述版本那样从结果中排除。

关于 LATERAL 的更多信息:

• Find most common elements in array with a group by
• Insert multiple rows in one table based on number in another table

精确结果

Postgres 8.4+

或者您分别处理时间范围的开始和结束以获得微秒级的精确结果。使查询更复杂，但成本更低且更准确:

WITH var AS (SELECT '08:00'::time  AS v_start
                  , '15:00'::time  AS v_end)
SELECT t_id
     , COALESCE(h.h, '0')  -- add / subtract fractions
       - CASE WHEN EXTRACT(ISODOW FROM t_start) < 6
               AND t_start::time > v_start
               AND t_start::time < v_end
         THEN t_start - date_trunc('hour', t_start)
         ELSE '0'::interval END
       + CASE WHEN EXTRACT(ISODOW FROM t_end) < 6
               AND t_end::time > v_start
               AND t_end::time < v_end
         THEN t_end - date_trunc('hour', t_end)
         ELSE '0'::interval END                 AS work_interval
FROM   t CROSS JOIN var
LEFT   JOIN (  -- count full hours, similar to above solutions
   SELECT t_id, count(*)::int * interval '1h' AS h
   FROM  (
      SELECT t_id, v_start, v_end
           , generate_series (date_trunc('hour', t_start)
                            , date_trunc('hour', t_end) - interval '1h'
                            , interval '1h') AS h
      FROM   t, var
      ) sub
   WHERE  EXTRACT(ISODOW FROM h) < 6
   AND    h::time >= v_start
   AND    h::time <= v_end - interval '1h'
   GROUP  BY 1
   ) h USING (t_id)
ORDER  BY 1;

db<> fiddle here
<子>旧sqlfiddle

Postgres 9.2+ tsrange

新的范围类型与 intersection operator * 相结合，为精确结果 提供了更优雅的解决方案。 :

时间范围仅跨越一天的简单函数:

CREATE OR REPLACE FUNCTION f_worktime_1day(_start timestamp, _end timestamp)
  RETURNS interval
  LANGUAGE sql IMMUTABLE AS
$func$  -- _start & _end within one calendar day! - you may want to check ...
SELECT CASE WHEN extract(ISODOW from _start) < 6 THEN (
   SELECT COALESCE(upper(h) - lower(h), '0')
   FROM  (
      SELECT tsrange '[2000-1-1 08:00, 2000-1-1 15:00)' -- hours hard coded
           * tsrange( '2000-1-1'::date + _start::time
                    , '2000-1-1'::date + _end::time ) AS h
      ) sub
   ) ELSE '0' END
$func$;

如果您的范围从不跨越多天，这就是您所需要的。
否则，使用这个包装函数来处理任何间隔:

CREATE OR REPLACE FUNCTION f_worktime(_start timestamp
                                    , _end timestamp
                                    , OUT work_time interval)
  LANGUAGE plpgsql IMMUTABLE AS
$func$
BEGIN
   CASE _end::date - _start::date  -- spanning how many days?
   WHEN 0 THEN                     -- all in one calendar day
      work_time := f_worktime_1day(_start, _end);
   WHEN 1 THEN                     -- wrap around midnight once
      work_time := f_worktime_1day(_start, NULL)
                +  f_worktime_1day(_end::date, _end);
   ELSE                            -- multiple days
      work_time := f_worktime_1day(_start, NULL)
                +  f_worktime_1day(_end::date, _end)
                + (SELECT count(*) * interval '7:00'  -- workday hard coded!
                   FROM   generate_series(_start::date + 1
                                        , _end::date   - 1, '1 day') AS t
                   WHERE  extract(ISODOW from t) < 6);
   END CASE;
END
$func$;

调用:

SELECT t_id, f_worktime(t_start, t_end) AS worktime
FROM   t
ORDER  BY 1;

db<> fiddle here
<子>旧sqlfiddle