Bagaimana untuk Mengira Waktu Bekerja Antara Dua Tarikh dalam PostgreSQL, Tidak Termasuk Hujung Minggu?

Kira Waktu Kerja Antara 2 Tarikh dalam PostgreSQL

Pernyataan Masalah

Reka bentuk algoritma untuk mengira waktu bekerja antara dua cap waktu, memandangkan hujung minggu bukan hari bekerja dan hari bekerja dikira dari 8 pagi hingga 3 pm.

Penyelesaian

Langkah 1: Kenal pasti Waktu Bekerja setiap Hari

Tentukan waktu bekerja sebagai hari bekerja dari 8 pagi hingga 3 petang:

WITH WorkingHours AS (
  SELECT '08:00'::time AS start_time, '15:00'::time AS end_time
)

Langkah 2: Kira Selang untuk Setiap Hari

Buat satu siri selang untuk setiap hari dalam julat tarikh yang diberikan:

SELECT t_id,
       generate_series(date_trunc('day', t_start), date_trunc('day', t_end), '1 day') AS day
FROM   t

Langkah 3: Kira Waktu Kerja untuk Setiap Selang

Interpolasi waktu kerja untuk setiap hari dan hitung jam pecahan menggunakan jenis julat 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

Langkah 4: Fungsi Mengira Pecahan Jam

Tentukan fungsi f_worktime untuk mengira jam pecahan:

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;

Contoh

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;

Output:

| t_id | working_hours |
|------|---------------|
|    0 |             3 |
|    1 |             8 |
|    2 |             3 |

Atas ialah kandungan terperinci Bagaimana untuk Mengira Waktu Bekerja Antara Dua Tarikh dalam PostgreSQL, Tidak Termasuk Hujung Minggu?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!