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

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

挑戰

計算兩個日期之間的工作時間可能很複雜，尤其是在考慮因素時例如週末和特定工作時間。在這篇文章中，我們將探索使用 PostgreSQL 有效處理此任務的幾種方法。

考慮週末和工作時間

假設我們的工作時間是從週一到週五，8 點之間上午 :00 和下午 3:00。使用這個定義，我們可以計算任兩個時間戳記之間的工作時間，如下所示：

1. 忽略週末：檢查日期是否在週末（週六或週日）並將其排除根據計算結果。
2. 截斷時間： 將時間戳轉換為僅包含日期和時間在工作時間內。例如，「2023-03-08 19:30:00」將變為「2023-03-08 15:00:00」。
3. 計算小時數：計算截斷的結束時間和截斷的開始時間。這將為您提供總工作時間。

PostgreSQL 解決方案

捨入結果

要取得捨入結果，我們可以使用PostgreSQL的generate_series(時間範圍內產生一系列以1小時為間隔的資料。然後，我們計算指定時間段內符合條件的間隔的數量。以下是查詢範例：

SELECT count(*) AS work_hours
FROM   generate_series('2023-03-08 14:00', '2023-03-09 09:00' - interval '1 hour', interval '1 hour') h
WHERE  EXTRACT(ISODOW FROM h) < 6
AND    h::time >= '08:00'
AND    h::time < '15:00';

更精確

要獲得更精確的結果，您可以使用更小的時間單位，例如5 分鐘增量。以下查詢提供5 分鐘精確度的結果：

SELECT count(*) * interval '5 min' AS work_interval
FROM   generate_series('2023-03-08 14:01', '2023-03-09 09:00' - interval '5 min', interval '5 min') h
WHERE  EXTRACT(ISODOW FROM h) < 6
AND    h::time >= '08:00'
AND    h::time < '15:00';

精確結果

對於精確結果，您可以透過單獨處理開始來採取更細緻的方法和時間範圍結束。這是一個提供精確到微秒的間隔結果的查詢：

SELECT t_id
     , COALESCE(h.h, '0')
       - 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 (
   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;

方法比較

所提供的不同方法提供不同等級的精確度和效能。

舍入結果：此方法易於實現並提供合理的估計，特別是當輸入時間接近工作邊界時小時範圍。
更精確：這種方法透過使用較小的時間單位提供更好的準確性。在大多數情況下，對性能的影響很小。
確切結果：此方法比較複雜，需要額外的計算。它提供了最精確的結果，但可能會帶來更高的計算成本。

結論

選擇合適的方法取決於所需的精確度和性能限制。對於通用用途，以 5 分鐘為增量的「更精確」方法在準確性和效率之間取得了良好的平衡。然而，對於絕對精度至關重要的情況，可以採用「精確結果」方法來提供精確到微秒的間隔。

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