Bagaimanakah Saya Boleh Mengira Waktu Kerja Antara Tarikh dalam PostgreSQL, Mempertimbangkan Hujung Minggu dan Waktu Kerja Tertentu?

Mengira Waktu Bekerja Antara Tarikh dalam PostgreSQL

Pengenalan

Dalam pelbagai senario, menentukan bilangan jam bekerja antara dua cap masa boleh terbukti penting dalam bidang seperti gaji dan penjadualan. Dalam PostgreSQL, pengiraan ini memerlukan pertimbangan yang teliti terhadap parameter khusus hari bekerja dan masa. Artikel ini menggariskan penyelesaian yang komprehensif, dengan mengambil kira kriteria berikut:

• Hujung Minggu (Sabtu dan Ahad) dikecualikan daripada waktu bekerja.
• Waktu bekerja adalah ditakrifkan sebagai Isnin hingga Jumaat, 8 pagi hingga 3 pm.
• Waktu pecahan hendaklah dimasukkan dalam pengiraan.

Penyelesaian

Kaedah 1: Bulat Keputusan untuk Hanya Dua Cap Masa

Pendekatan ini beroperasi pada unit 1 jam, mengabaikan jam pecahan. Ia adalah kaedah yang mudah tetapi kurang tepat.

Pertanyaan:

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

Contoh Input:

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

Output:

2

Kaedah 2: Keputusan Bulat untuk Jadual Cap Masa

Pendekatan ini memanjangkan kaedah sebelumnya untuk mengendalikan jadual cap masa berpasangan.

Pertanyaan:

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;

Kaedah 3: Pengiraan Lebih Tepat

Untuk pengiraan yang lebih halus, unit masa yang lebih kecil boleh dipertimbangkan.

Pertanyaan:

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;

Contoh Input:

| 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    |

Output:

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

Kaedah 4: Tepat Keputusan

Pendekatan ini memberikan hasil yang tepat dengan ketepatan mikrosaat. Ia lebih kompleks tetapi lebih cekap dari segi pengiraan.

Pertanyaan:

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;

Penyelesaian komprehensif ini menangani keperluan untuk mengira waktu bekerja dengan tepat dan cekap dalam PostgreSQL.

Atas ialah kandungan terperinci Bagaimanakah Saya Boleh Mengira Waktu Kerja Antara Tarikh dalam PostgreSQL, Mempertimbangkan Hujung Minggu dan Waktu Kerja Tertentu?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!