PostgreSQL で 2 つの日付の間の労働時間を正確に計算するにはどうすればよいですか?

PostgreSQL で 2 つの日付間の労働時間を計算する方法

課題

2 つの日付間の労働時間の計算は、特に要因を考慮すると複雑になる場合があります週末や特定の勤務時間など。この投稿では、PostgreSQL を使用してこのタスクを効率的に処理するためのいくつかのアプローチを検討します。

週末と労働時間を考慮する

労働時間が月曜から金曜の 8 時の間であると仮定しましょう。午前と午後3時。この定義を使用すると、次のように任意の 2 つのタイムスタンプ間の労働時間を計算できます:

1. 週末を無視: 日付が週末 (土曜日または日曜日) に該当するかどうかを確認し、それらを除外します。
2. Truncate hours: のみを含むようにタイムスタンプを変換します。勤務時間内の日付と時刻。たとえば、「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 分単位の「More Precision」方式が精度と効率のバランスに優れています。ただし、絶対的な精度が最優先される場合は、「正確な結果」アプローチを使用して、マイクロ秒単位の正確な間隔を提供できます。

以上がPostgreSQL で 2 つの日付の間の労働時間を正確に計算するにはどうすればよいですか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。