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