PostgreSQL での日付間の労働時(shí)間の計(jì)算

はじめに

さまざまなシナリオで、 2 つのタイムスタンプ間の労働時(shí)間數(shù)は、給與計(jì)算や給與計(jì)算などの分野で重要であることがわかります。スケジューリング。 PostgreSQL では、この計(jì)算には曜日と時(shí)間固有のパラメーターを慎重に考慮する必要があります。この記事では、次の基準(zhǔn)を考慮した包括的なソリューションの概要を説明します:

• 週末 (土曜日と日曜日) は労働時(shí)間から除外されます。
• 労働時(shí)間は月曜日から金曜日、午前 8 時(shí)から午後 3 時(shí)までと定義pm.
• 小數(shù)時(shí)間は計(jì)算に含まれます。

解決策

方法 1: 四捨五入タイムスタンプが 2 つだけの結(jié)果

このアプローチは機(jī)能します時(shí)間の小數(shù)點(diǎn)以下は無視して、1 時(shí)間単位で表示されます。これは単純ですが、精度はそれほど高くありません。

クエリ:

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: タイムスタンプのテーブルの丸められた結(jié)果

このアプローチは、タイムスタンプのテーブルを処理するために前のメソッドを拡張します。ペア。

クエリ:

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;

方法 3: より正確な計(jì)算

より詳細(xì)な計(jì)算の場(chǎng)合は、より小さな時(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é)果を提供します。より複雑ですが、計(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;

この包括的なソリューションは、PostgreSQL で労働時(shí)間を正確かつ効率的に計(jì)算するニーズに対応します。

以上が週末と特定の労働時(shí)間を考慮して、PostgreSQL で日付間の労働時(shí)間を計(jì)算するにはどうすればよいですか?の詳細(xì)內(nèi)容です。詳細(xì)については、PHP 中國(guó)語(yǔ) Web サイトの他の関連記事を參照してください。