Bagaimanakah `tsrange` PostgreSQL Boleh Memudahkan Pertanyaan untuk Waktu Operasi Perniagaan?

Waktu Pertanyaan Operasi dalam PostgreSQL Menggunakan tsrange

Pengenalan

Dalam PostgreSQL, jam operasi untuk entiti tertentu boleh disimpan sebagai pasangan lajur integer, seperti opens_on dan closes_on, mewakili hari bekerja, dan opens_at dan closes_at, mewakili masa dalam hari. Walau bagaimanapun, melaksanakan pertanyaan pada data sedemikian boleh menjadi rumit kerana kemungkinan pertindihan dan penyelewengan dalam jadual.

Pendekatan Alternatif dengan tsrange

Untuk menangani kerumitan ini, kaedah yang lebih cekap pendekatan melibatkan penggunaan jenis data tsrange, yang mewakili julat cap masa tanpa zon masa. Dengan menggunakan tsrange, kami boleh menyimpan waktu operasi sebagai set julat. Pendekatan ini menawarkan beberapa faedah:

Struktur Data

CREATE TABLE hoo (
  hoo_id  serial PRIMARY KEY,
  shop_id int NOT NULL, -- REFERENCES shop(shop_id)
  hours   tsrange NOT NULL
);

Sebagai contoh, jika kedai dibuka dari hari Rabu 6 petang hingga Khamis 5 pagi UTC, waktu yang sepadan julat akan jadi:

'[1996-01-03 18:30, 1996-01-04 05:00]'

Fungsi

CREATE OR REPLACE FUNCTION f_hoo_time(timestamptz)
  RETURNS timestamp
  LANGUAGE sql IMMUTABLE PARALLEL SAFE AS
$func$
SELECT timestamp '1996-01-01' + ( AT TIME ZONE 'UTC' - date_trunc('week',  AT TIME ZONE 'UTC'))
$func$;

CREATE OR REPLACE FUNCTION f_hoo_hours(_from timestamptz, _to timestamptz)
  RETURNS TABLE (hoo_hours tsrange)
  LANGUAGE plpgsql IMMUTABLE PARALLEL SAFE COST 500 ROWS 1 AS
$func$
DECLARE
   ts_from timestamp := f_hoo_time(_from);
   ts_to   timestamp := f_hoo_time(_to);
BEGIN
   -- Perform sanity checks and split ranges at Sunday midnight if necessary.
   ...
END
$func$;

Pertanyaan

Pertanyaan kompleks asal kini boleh digantikan dengan yang dipermudahkan berikut pernyataan:

SELECT *
FROM hoo
WHERE hours @> f_hoo_time(now());

Pengindeksan

Untuk mengoptimumkan prestasi pertanyaan, indeks SP-GiST pada jam boleh dibuat:

CREATE INDEX hoo_hours_spgist_idx on hoo USING spgist (hours);

Prestasi

Pendekatan menggunakan indeks SP-GiST ini menawarkan peningkatan prestasi yang ketara, terutamanya apabila mencari hasil yang banyak. Ia mengatasi strategi pengindeksan lain dan mengurangkan masa pelaksanaan untuk kedua-dua kiraan hasil rendah dan tinggi.

Atas ialah kandungan terperinci Bagaimanakah `tsrange` PostgreSQL Boleh Memudahkan Pertanyaan untuk Waktu Operasi Perniagaan?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!