如何計算 PostgreSQL 中兩個日期之間的工作時間？

計算 PostgreSQL 中兩個日期之間的工作時間

計算特定時間戳之間的工作時間時，需要考慮週末和指定的工作時間。在 PostgreSQL 中，我們可以利用各種技術來完成此任務。

舍入結果

對於特定時間戳範圍：

以 1 小時為單位，忽略分數。公式如下：

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

對於整個表：

-- Table Creation and Data Insertion
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')
, (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');

-- Main Query
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;

更精確的結果

要獲得更高的精度，請使用較小的數值時間單位，例如5 分鐘切片。

-- Precision with 5-minute Slices
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 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)

以上是如何計算 PostgreSQL 中兩個日期之間的工作時間？的詳細內容。更多資訊請關注PHP中文網其他相關文章！