Wie kann „tsrange' von PostgreSQL Abfragen für Geschäftszeiten vereinfachen?

Betriebsstundenabfrage in PostgreSQL mit tsrange

Einführung

In PostgreSQL die Stunden Der Vorgang für eine bestimmte Entität kann als Paare ganzzahliger Spalten gespeichert werden, z. B. „opens_on“ und „closes_on“, die darstellen der Wochentag und „opens_at“ und „closes_at“, die die Tageszeit darstellen. Allerdings kann die Durchführung von Abfragen für solche Daten aufgrund möglicher Überschneidungen und Unregelmäßigkeiten im Zeitplan komplex sein.

Alternativer Ansatz mit tsrange

Um diese Komplexitäten zu bewältigen, ist ein effizienterer Ansatz erforderlich Der Ansatz beinhaltet die Verwendung des Datentyps tsrange, der einen Bereich von Zeitstempeln ohne Zeitzonen darstellt. Durch die Verwendung von tsrange können wir Betriebsstunden als Bereichssätze speichern. Dieser Ansatz bietet mehrere Vorteile:

Datenstruktur

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

Wenn beispielsweise ein Geschäft von Mittwoch 18:00 Uhr bis Donnerstag 5:00 Uhr UTC geöffnet ist, gelten die entsprechenden Öffnungszeiten Reichweite würde sein:

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

Funktionen

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

Abfrage

Die ursprüngliche komplexe Abfrage kann jetzt sein durch die folgende Vereinfachung ersetzt Aussage:

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

Indexierung

Um die Abfrageleistung zu optimieren, kann ein SP-GiST-Index für Stunden erstellt werden:

CREATE INDEX hoo_hours_spgist_idx on hoo USING spgist (hours);

Leistung

Dieser Ansatz unter Verwendung des SP-GiST-Index bietet erhebliche Leistungsverbesserungen, insbesondere bei der Suche nach großen Zahlen der Ergebnisse. Es übertrifft andere Indexierungsstrategien und reduziert die Ausführungszeit sowohl für niedrige als auch für hohe Ergebniszahlen.

Das obige ist der detaillierte Inhalt vonWie kann „tsrange' von PostgreSQL Abfragen für Geschäftszeiten vereinfachen?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!