計(jì)算 PostgreSQL 中日期之間的工作時(shí)間

簡(jiǎn)介

在各種場(chǎng)景中，確定兩個(gè)時(shí)間戳之間的工作小時(shí)數(shù)在工資單和調(diào)度等領(lǐng)域被證明是至關(guān)重要的。在 PostgreSQL 中，此計(jì)算需要仔細(xì)考慮工作日和特定時(shí)間的參數(shù)。本文概述了一個(gè)全面的解決方案，考慮到以下標(biāo)準(zhǔn)：

• 周末（周六和周日） 不計(jì)入工作時(shí)間。
• 工作時(shí)間定義為周一至周五，上午 8 點(diǎn)至凌晨 3 點(diǎn)pm.
• 小數(shù)小時(shí)將包含在計(jì)算中。

解決方案

方法1：四舍五入僅兩個(gè)時(shí)間戳的結(jié)果

此方法適用于以 1 小時(shí)為單位，忽略小數(shù)小時(shí)。這是一種簡(jiǎn)單但不太精確的方法。

查詢：

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 &amp;lt;= '14:00';

示例輸入：

2013-06-24 13:30, 2013-06-24 15:29

輸出：

2

方法 2：時(shí)間戳表的舍入結(jié)果

這種方法擴(kuò)展了之前的方法來處理時(shí)間戳表

查詢：

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;

方法三：更精確的計(jì)算

對(duì)于更細(xì)粒度的計(jì)算，更小的時(shí)間單位可以是已考慮。

查詢：

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'
GROUP  BY 1
ORDER  BY 1;

示例輸入：

| t_id | t_start                | t_end                  |
|------|-------------------------|-------------------------|
| 1    | 2009-12-03 14:00:00    | 2009-12-04 09:00:00    |
| 2    | 2009-12-03 15:00:00    | 2009-12-07 08:00:00    |
| 3    | 2013-06-24 07:00:00    | 2013-06-24 12:00:00    |
| 4    | 2013-06-24 12:00:00    | 2013-06-24 23:00:00    |
| 5    | 2013-06-23 13:00:00    | 2013-06-25 11:00:00    |
| 6    | 2013-06-23 14:01:00    | 2013-06-24 08:59:00    |

輸出：

| t_id | work_interval |
|------|----------------|
| 1    | 1 hour         |
| 2    | 8 hours        |
| 3    | 0 hours        |
| 4    | 0 hours        |
| 5    | 6 hours        |
| 6    | 1 hour         |

方法 4：精確結(jié)果

此方法提供微秒精度的精確結(jié)果。它更復(fù)雜，但計(jì)算效率更高。

查詢：

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)
ORDER  BY 1;

這個(gè)全面的解決方案解決了在 PostgreSQL 中準(zhǔn)確高效地計(jì)算工作時(shí)間的需求。

以上是如何計(jì)算 PostgreSQL 中日期之間的工作時(shí)間（考慮周末和特定工作時(shí)間）？的詳細(xì)內(nèi)容。更多信息請(qǐng)關(guān)注PHP中文網(wǎng)其他相關(guān)文章！