如何计算 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中文网其他相关文章！