Ich bin im RoR-Stack und ich musste etwas SQL schreiben, um diese Abfrage für alle Datensätze abzuschließen, die "offen" sind, was bedeutet, dass die aktuelle Zeit innerhalb der angegebenen Betriebsstunden liegt. In der Tabelle %code% speichern zwei %code% Spalten %code% und %code% einen Wochentag, und zwei %code% Felder %code% und %code% speichern die jeweilige Tageszeit.
Ich habe eine Abfrage erstellt, die das aktuelle Datum und die aktuelle Uhrzeit mit den gespeicherten Werten vergleicht, aber ich frage mich, ob es eine Möglichkeit gibt, zu einem bestimmten Datumstyp zu wechseln und PostgreSQL den Rest erledigt?
Das Fleisch der Abfrage ist:
%Vor%Der Grund für solch eine dichte Komplexität ist, dass eine Betriebsstunde um das Ende der Woche herumgehen kann, zum Beispiel beginnend am Mittag am Sonntag und bis Montag um 6 Uhr morgens. Da ich Werte in UTC speichere, gibt es viele Fälle, in denen die lokale Zeit des Benutzers auf sehr seltsame Weise umbrechen könnte. Die obige Abfrage stellt sicher, dass Sie ANY zwei mal der Woche eingeben können und wir den Umbruch kompensieren.
Gestalten Sie die Tabelle neu und speichern Sie die Öffnungszeiten (Betriebsstunden) als eine Gruppe von %code% (Bereich der Zeitmarke ohne Zeitzone) Werte. Benötigt Postgres 9.2 oder höher .
Wählen Sie eine zufällige Woche, um Ihre Öffnungszeiten zu planen. Ich mag die Woche:
1996-01-01 (Montag) bis 1996-01-07 (Sonntag)
Das ist das letzte Schaltjahr, in dem der 1. Januar zufällig ein Montag ist. Aber es kann jede zufällige Woche für diesen Fall sein. Sei nur konsequent.
Installieren Sie zuerst das zusätzliche Modul %code% . Warum?
%Vor%Erstellen Sie die Tabelle wie folgt:
%Vor%Die one Spalte %code% ersetzt alle Ihre Spalten:
%Vor%Beispielsweise werden die Betriebsstunden von Mittwoch, 18:30 bis Donnerstag, 05:00 UTC wie folgt eingegeben:
%Vor%Die Ausschlussbedingung %code% verhindert überlappende Einträge pro Shop. Es wird mit einem GiST-Index implementiert, der auch Ihre Abfrage unterstützt. Betrachten Sie das Kapitel "Index und Leistung" , in dem die Indexierungsstrategie erläutert wird.
Die Check-Einschränkung %code% erzwingt inklusive Grenzen für Ihre Bereiche, mit zwei bemerkenswerten Konsequenzen:
Die Check-Einschränkung %code% erzwingt die äußeren Grenzen der Staging-Woche mit dem " Bereich ist enthalten durch "operator %code% .
Bei einschließlich -Begrenzungen müssen Sie einen Sonderfall beachten, bei dem die Zeit um Sonntag Mitternacht herumgeht:
%Vor%Sie müssen beide Zeitstempel gleichzeitig suchen. Hier ist ein ähnlicher Fall mit exklusiver Obergrenze, der diesen Mangel nicht aufweist:
Um eine gegebene %code% zu normalisieren:
%Vor%Die Funktion akzeptiert %code% und gibt %code% zurück. Es fügt das verstrichene Intervall der jeweiligen Woche %code% in UTC-Zeit (!) Zum Startpunkt unserer Staging-Woche hinzu. ( %code% + %code% erzeugt %code% .)
Um Bereiche zu normalisieren und diese zu teilen, die Mon 00:00 kreuzen. Diese Funktion benötigt ein beliebiges Intervall (als zwei %code% ) und erzeugt ein oder zwei normalisierte %code% -Werte. Es umfasst beliebige legale Eingabe und verbietet den Rest:
%Vor%To %code% a einzelne Eingabezeile:
%Vor%Dies führt zu zwei Zeilen, wenn der Bereich um Mo 00:00 geteilt werden muss.
To %code% mehrere Eingabezeilen:
%Vor%Über den impliziten %code% Join:
Mit dem angepassten Design kann Ihre gesamte große, komplexe, teure Abfrage durch ... ersetzt werden:
%code%
%code%
%code%
Für ein wenig Spannung habe ich eine Spoilerplatte über die Lösung gelegt. Bewegen Sie die Maus über .
Die Abfrage wird durch den GiST-Index und schnell unterstützt, sogar für große Tabellen.
SQL Fiddle (mit weiteren Beispielen).
Wenn Sie die Gesamtöffnungszeiten (pro Geschäft) berechnen möchten, hier ist ein Rezept:
Der Begrenzungsoperator für Bereichstypen kann mit GiST oder SP-GiST index. Beide können verwendet werden, um eine Ausschlussbeschränkung zu implementieren, aber nur GiST unterstützt mehrspaltige Indizes :
Derzeit unterstützen nur die B-Tree-, GiST-, GIN- und BRIN-Indextypen mehrspaltige Indizes.
Und die Reihenfolge der Indexspalten ist wichtig :
Ein mehrspaltiger GiST-Index kann mit diesen Abfragebedingungen verwendet werden Beteiligen Sie jede Teilmenge der Spalten des Index. Bedingungen für zusätzliche Spalten beschränken die vom Index zurückgegebenen Einträge, aber die Bedingung auf der ersten Spalte ist der wichtigste für die Bestimmung, wie viel des Index muss gescannt werden. Ein GiST-Index wird relativ sein unwirksam, wenn seine erste Spalte nur einige eindeutige Werte aufweist wenn es viele verschiedene Werte in zusätzlichen Spalten gibt.
Wir haben also widerstreitende Interessen hier. Bei großen Tabellen gibt es viel mehr unterschiedliche Werte für %code% als für %code% .
Mein Skript zum Erzeugen von Dummy-Daten:
%Vor%Ergebnisse in 141k zufällig generierten Zeilen, 30k distinct %code% , 12k distinct %code% . (In der Regel ist der Unterschied größer.) Tabellengröße 8 MB.
Ich habe die Ausschlussbeschränkung gelöscht und neu erstellt:
%Vor%%code% ist zuerst ~ 4x schneller.
Zusätzlich habe ich zwei weitere für die Leseleistung getestet:
%Vor%Nach %code% habe ich zwei Abfragen ausgeführt:
Ich habe einen index-only-Scan für jeden (außer natürlich für "no index"):
%Vor%Wenn Sie viel mehr lesen als Sie schreiben (typischer Anwendungsfall), behalten Sie die Ausschlussbeschränkung, wie zu Beginn vorgeschlagen, und erstellen Sie einen zusätzlichen SP-GiST-Index, um die Leseleistung zu optimieren.