如何計算 PostgreSQL 中兩個日期之間的工作時間（不包括週末）？

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

問題陳述

設計一個演算法來計算兩個時間戳記之間的工作時間，考慮到週末不是工作日和工作日從早上8 點到下午 3點計算

解決方案

第1 步：決定每天的工作時間

將工作時間定義為工作日上午8 點到下午3 點：

WITH WorkingHours AS (
  SELECT '08:00'::time AS start_time, '15:00'::time AS end_time
)

步驟2：計算每天的間隔

在範圍內為每天創建一系列間隔給定日期範圍：

SELECT t_id,
       generate_series(date_trunc('day', t_start), date_trunc('day', t_end), '1 day') AS day
FROM   t

第3 步：計算每個時間間隔的工作時間

插入每天的工作時間並使用範圍類型tsrange 計算小數小時：

SELECT t_id,
       SUM(CASE
         WHEN EXTRACT(ISODOW FROM day) < 6 THEN
           COALESCE(
             f_worktime(day::timestamp + WorkingHours.start_time, day::timestamp + WorkingHours.end_time),
             '0'
           )
         ELSE
           '0'
       END) AS work_time
FROM   WorkingHours
CROSS JOIN (
  SELECT t_id, day
  FROM   temp
) AS temp
GROUP  BY 1

第4 步：計算分數的函數小時

定義一個函數f_worktime來計算小數小時：

CREATE FUNCTION f_worktime(_start timestamp, _end timestamp)
RETURNS interval
LANGUAGE sql AS
$func$
SELECT COALESCE(upper(tsrange(_start::timestamp, _end::timestamp)) - lower(tsrange(_start::timestamp, _end::timestamp)), '0')::interval;
$func$
IMMUTABLE;

範例

WITH t AS (
  SELECT 0 AS t_id, '2023-05-01 09:00'::timestamp AS t_start, '2023-05-01 11:30'::timestamp AS t_end
UNION ALL
  SELECT 1, '2023-05-02 10:00'::timestamp, '2023-05-02 18:00'::timestamp
UNION ALL
  SELECT 2, '2023-05-03 09:15'::timestamp, '2023-05-04 10:45'::timestamp
)
SELECT t.t_id, extract(hour from work_time) AS working_hours
FROM   t
CROSS JOIN (
  SELECT t_id, SUM(CASE WHEN EXTRACT(ISODOW FROM day) < 6 THEN f_worktime(day::timestamp + WorkingHours.start_time, day::timestamp + WorkingHours.end_time) ELSE '0' END) AS work_time
  FROM   WorkingHours
  CROSS JOIN (
    SELECT t_id, day
    FROM   (
      SELECT t_id,
             generate_series(date_trunc('day', t_start), date_trunc('day', t_end), '1 day') AS day
      FROM   t
    ) AS temp
  ) AS temp
  GROUP  BY 1
) AS temp;

輸出：

| t_id | working_hours |
|------|---------------|
|    0 |             3 |
|    1 |             8 |
|    2 |             3 |

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