Bagaimana Mengira Waktu Kerja Dengan Tepat Antara Dua Tarikh dalam PostgreSQL?

Cara Mengira Waktu Bekerja Antara 2 Tarikh dalam PostgreSQL

Cabarannya

Mengira waktu kerja antara dua tarikh boleh menjadi rumit, terutamanya apabila mempertimbangkan faktor seperti hujung minggu dan waktu kerja tertentu. Dalam siaran ini, kami akan meneroka beberapa pendekatan menggunakan PostgreSQL untuk mengendalikan tugas ini dengan cekap.

Mengambil Kira Hujung Minggu dan Waktu Bekerja

Anggaplah waktu bekerja kami adalah dari Isnin hingga Jumaat, antara 8 :00 PAGI dan 3:00 PETANG. Menggunakan takrifan ini, kita boleh mengira waktu bekerja antara mana-mana dua cap waktu seperti berikut:

1. Abaikan Hujung Minggu: Semak sama ada tarikh jatuh pada hujung minggu (Sabtu atau Ahad) dan kecualikan tarikh tersebut daripada pengiraan.
2. Truncate Waktu: Tukar cap waktu untuk memasukkan sahaja tarikh dan masa dalam masa bekerja. Contohnya, "2023-03-08 19:30:00" akan menjadi "2023-03-08 15:00:00".
3. Kira Jam: Kira perbezaan antara masa tamat terpenggal dan masa mula terpotong. Ini akan memberikan anda jumlah jam bekerja.

Penyelesaian PostgreSQL

Hasil Dibundarkan

Untuk mendapatkan hasil yang dibundarkan, kita boleh menggunakan Fungsi generate_series() PostgreSQL untuk menjana satu siri selang 1 jam dalam julat waktu bekerja. Kami kemudian mengira bilangan selang yang layak jatuh dalam tempoh masa yang ditentukan. Berikut ialah contoh pertanyaan:

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

Lebih Ketepatan

Untuk hasil yang lebih tepat, anda boleh menggunakan unit masa yang lebih kecil, seperti kenaikan 5 minit. Pertanyaan berikut memberikan hasil dengan ketepatan 5 minit:

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

Keputusan Tepat

Untuk hasil yang tepat, anda boleh mengambil pendekatan yang lebih bernuansa dengan mengendalikan permulaan secara berasingan dan akhir tempoh masa. Berikut ialah pertanyaan yang memberikan hasil selang masa yang tepat kepada mikrosaat:

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;

Perbandingan Pendekatan

Pendekatan berbeza yang dibentangkan memberikan pelbagai tahap ketepatan dan prestasi.

Keputusan Bulat: Kaedah ini mudah dilaksanakan dan memberikan anggaran yang munasabah, terutamanya apabila masa input hampir dengan sempadan julat waktu bekerja.
Lebih Ketepatan: Pendekatan ini menawarkan ketepatan yang lebih baik dengan menggunakan unit masa yang lebih kecil. Kesan ke atas prestasi adalah minimum dalam kebanyakan senario.
Keputusan Tepat: Kaedah ini lebih kompleks dan memerlukan pengiraan tambahan. Ia memberikan hasil yang paling tepat tetapi mungkin datang pada kos pengiraan yang lebih tinggi.

Kesimpulan

Memilih pendekatan yang sesuai bergantung pada ketepatan yang diperlukan dan kekangan prestasi. Untuk kegunaan umum, kaedah "Lebih Ketepatan" dengan kenaikan 5 minit mencapai keseimbangan yang baik antara ketepatan dan kecekapan. Walau bagaimanapun, untuk kes di mana ketepatan mutlak adalah yang terpenting, pendekatan "Hasil Tepat" boleh digunakan untuk memberikan selang masa yang tepat kepada mikrosaat.

Atas ialah kandungan terperinci Bagaimana Mengira Waktu Kerja Dengan Tepat Antara Dua Tarikh dalam PostgreSQL?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!