Generieren von Zeitreihen zwischen zwei Datumsangaben in PostgreSQL

Es gibt zwei Antworten (bis jetzt). Beide funktionieren, aber beide sind nicht optimal. Hier ist ein dritter:

SELECT day::date 
FROM   generate_series(timestamp '2004-03-07'
                     , timestamp '2004-08-16'
                     , interval  '1 day') day;

• Keine zusätzliche date_trunc(). Die Umwandlung in date (day::date) führt dies implizit aus.

• Es hat aber auch keinen Sinn, Datumsliterale an date als Eingabeparameter zu übergeben. Au contraire, timestamp ist hier die beste Wahl. Der Leistungsvorteil ist gering, aber es gibt keinen Grund, es nicht zu nehmen. Und Sie verwenden nicht unnötig DST-Regeln, die mit dem Datentyp timestamp with time zone gekoppelt sind. Siehe die Erklärung unten.

Kürzere Entsprechung:

SELECT day::date 
FROM   generate_series(timestamp '2004-03-07', '2004-08-16', '1 day') day;

Oder auch mit der set-returning-Funktion in der Liste SELECT:

SELECT generate_series(timestamp '2004-03-07', '2004-08-16', '1 day')::date AS day;

Das Schlüsselwort AS ist hier erforderlich, da sonst der Spaltenaliasname day missverstanden würde.

Ich würde nicht raten, die letzte vor Postgres 10 zu verwenden, zumindest nicht mit mehr als einer set-returning-Funktion in derselben SELECT-Liste. Sehen:

• Wie ist das erwartete Verhalten für mehrere set-returning-Funktionen in select-Klauseln?

Warum?

Es gibt eine Reihe überladener Varianten von generate_series() . Derzeit (Postgres 10):

SELECT oid::regprocedure   AS function_signature
     , prorettype::regtype AS return_type
FROM   pg_proc
where  proname = 'generate_series';

 function_signature | return_type 
: ------------------------------------------- ----------------------------------- | : --------------------------
 generate_series (Ganzzahl, Ganzzahl, Ganzzahl) | Ganzzahl 
 Generator_Series (Ganzzahl, Ganzzahl) | Ganzzahl 
 generate_series (bigint, bigint, bigint) | bigint 
 generate_series (bigint, bigint) | bigint 
 generate_series (numerisch, numerisch, numerisch) | numerisch 
 generate_series (numerisch, numerisch) | numerisch 
generate_series (Zeitstempel ohne Zeitzone, Zeitstempel ohne Zeitzone, Intervall) | Zeitstempel ohne Zeitzone 
 generate_series (Zeitstempel mit Zeitzone, Zeitstempel mit Zeitzone, Intervall) | Zeitstempel mit Zeitzone

Die Variante, die numeric entgegennimmt und zurückgibt, wurde mit Postgres 9.5 hinzugefügt. Die einzig relevanten sind hier die letzten beiden in Fettdruck, die timestamptimestamptz NEHMEN UND ZURÜCKGEBEN.

WIE SIE SEHEN, GIBT ES _/KEINE VARIANTE, DIE date nimmt oder zurückgibt. Deshalb brauchen wir eine explizite Besetzung, wenn wir date zurückgeben wollen. Durch das Übergeben von timestamp wird die rechte Funktion direkt aufgelöst, ohne in Auflösungsregeln für Funktionstypen absteigen zu müssen und ohne zusätzliche Umwandlung für die Eingabe.

Und timestamp '2004-03-07' ist vollkommen gültig. Der Zeitabschnitt ist standardmäßig 00:00, wenn er weggelassen wird.

Dank Funktionstyp Auflösung können wir noch date übergeben. Dies erfordert jedoch mehr Arbeit von Postgres. Es gibt eine implizite Umwandlung von date zu timestamp sowie von date zu timestamptz. Wäre mehrdeutig, aber timestamptz ist "bevorzugt" unter "Datums-/Zeittypen". Das Übereinstimmung wird in Schritt 4d entschieden. :

Führen Sie alle Kandidaten durch und behalten Sie diejenigen, die bevorzugte Typen akzeptieren (der Typkategorie des Eingabedatentyps) an den meisten Stellen, an denen Typumwandlung ist erforderlich. Bewahren Sie alle Kandidaten auf, wenn keiner akzeptiert bevorzugte Typen. Wenn nur ein Kandidat übrig bleibt, verwenden Sie ihn. sonst weiter zum nächsten Schritt.

Zusätzlich zu der zusätzlichen Arbeit in der Auflösung von Funktionstypen wird timestamptz eine zusätzliche Umwandlung hinzugefügt. Die Umwandlung in timestamptz erhöht nicht nur die Kosten, sondern führt auch zu Problemen mit der Sommerzeit (DST), was in seltenen Fällen zu unerwarteten Ergebnissen führt. (DST ist ein schwachsinniges Konzept, übrigens kann ich das nicht genug betonen.)

• Wie erstelle ich eine Datumsreihe in PostgreSQL?
• Wie generiere ich eine Zeitreihe in PostgreSQL?

Ich habe der Geige Demos hinzugefügt, um den teureren Abfrageplan zu zeigen:

dbfiddle hier

Verbunden:

• Gibt es eine Möglichkeit, die Funktionsüberladung in Postgres zu deaktivieren
• Datumsreihe generieren - Datumstyp als Eingabe verwenden
• Postgres-Datentypumwandlung

Sie können Serien direkt mit Datum erstellen. Keine Notwendigkeit, Ints oder Zeitstempel zu verwenden:

select date::date 
from generate_series(
  '2004-03-07'::date,
  '2004-08-16'::date,
  '1 day'::interval
) date;

du kannst gerne verwenden

wählen Sie generate_series ('2012-12-31' :: Zeitmarke, '2018-10-31' :: Zeitmarke, '1 Tag' :: Intervall) :: Datum aus