Generierung von Zeitreihen, die zwischen zwei Daten in PostgreSQL

Ich habe eine Abfrage wie diese, die schön ist, generiert eine Reihe von Daten, die zwischen 2 angegebenen Terminen:

select date '2004-03-07' + j - i as AllDate 
from generate_series(0, extract(doy from date '2004-03-07')::int - 1) as i,
     generate_series(0, extract(doy from date '2004-08-16')::int - 1) as j

Erzeugt es 162 Termine zwischen 2004-03-07 und 2004-08-16 und das, was ich will. Das problem bei diesem code ist, dass es nicht die richtige Antwort, wenn die beiden Termine sind aus verschiedenen Jahren, zum Beispiel, wenn ich versuche 2007-02-01 und 2008-04-01.

Gibt es eine bessere Lösung?

InformationsquelleAutor der Frage f.ashouri | 2013-01-01

  1. 86

    Kann getan werden, ohne eine Konvertierung in/aus int - (aber an/aus timestamp statt)

    SELECT date_trunc('day', dd):: date
FROM generate_series
        ( '2007-02-01'::timestamp 
        , '2008-04-01'::timestamp
        , '1 day'::interval) dd
        ;

    InformationsquelleAutor der Antwort wildplasser

  2. 27

    Können Sie generieren Serie direkt mit Termin. Keine Notwendigkeit für die Verwendung von ints oder Zeitstempel:

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

    InformationsquelleAutor der Antwort fbonetti

  3. 25

    Gibt es zwei Antworten (so weit). Beide arbeiten, aber beide sind suboptimal. Hier ist ein drittes:

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

    • Keine Notwendigkeit für eine zusätzliche date_trunc(). Der cast zu date (day::date) bedeutet, dass implizit.

    • Aber es gibt auch keinen Punkt in der casting-Datums-Literale in date als input-parameter. Au contraire, timestamp ist die beste Wahl hier. Der Vorteil in der Leistung klein ist, aber es gibt keinen Grund, es nicht zu nehmen. Und Sie nicht unnötig umfassen Sommerzeit-Regeln gepaart mit dem Datentyp timestamp with time zone. Siehe Erklärung weiter unten.

    Kürzer, gleichwertig:

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

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

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

    Den AS Schlüsselwort ist erforderlich hier, da die Spalte alias day würde missverstanden werden sonst.

    Ich würde nicht raten, zu verwenden, die Letzte vor Postgres-10, zumindest nicht mit mehr als einem set-returning Funktion in der gleichen SELECT Liste. Siehe:

    Warum?

    Gibt es eine Reihe von überladenen Varianten 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(integer,integer,integer) | ganze Zahl 
generate_series(integer,integer) | ganze Zahl 
generate_series(bigint,bigint,bigint) | bigint 
generate_series(bigint,bigint) | bigint 
generate_series(numeric,numeric,numeric) | numerische 
generate_series(numeric,numeric) | numerische 
generate_series(Zeitstempel ohne Zeitzone,Zeitstempel ohne Zeitzone,Intervall) | Zeitstempel ohne Zeitzone 
generate_series(timestamp with time zone,timestamp with time zone interval) | timestamp with time zone

    Variante nehmen und die Rückkehr numeric wurde Hinzugefügt, mit Postgres 9.5. Aber das nur relevant, hier sind die letzten zwei Fett nehmen und die Rückkehr timestamp /timestamptz.

    Wie Sie sehen können, gibt es keine Variante nehmen oder die Rückkehr date. Deshalb brauchen wir eine explizite cast, wenn wir zurückkehren wollen date. Vorbei timestamp wird die right-Funktion direkt, ohne abzusteigen, in Funktion Typ Auflösung Regeln und ohne zusätzliche Besetzung für den input.

    Sowie timestamp '2004-03-07' ist vollkommen gültig. Der Zeit-Teil wird standardmäßig auf 00:00 wenn Sie weggelassen werden.

    Dank Funktion Typ Auflösung können wir passieren noch date. Aber das erfordert mehr Arbeit von Postgres. Es ist ein implizite cast von date zu timestamp sowie von date zu timestamptz. Wäre mehrdeutig, aber timestamptz ist "bevorzugt" unter "Datum/Zeit-Typen". Also die match ist entschieden zu Schritt 4d.:

    Durchlaufen alle Kandidaten und behalte die, annehmen bevorzugte Typen
    (der input-Daten-Typ s-Typ-Kategorie) an den meisten stellen, wo
    Typ-Konvertierung erforderlich. Halten Sie alle Kandidaten, wenn keiner akzeptieren
    bevorzugte Arten. Wenn nur ein Kandidat bleibt, verwenden Sie es; anders weiter
    zum nächsten Schritt.

    Neben der zusätzlichen Arbeit in der Funktion Typ Auflösung dies fügt eine zusätzliche Besetzung zu timestamptz. Der cast zu timestamptz bietet nicht nur mehr Kosten, es kann auch zu Problemen führen, mit daylight saving time (DST), was zu unerwarteten Ergebnissen in seltenen Fällen. (DST ist ein Schwachsinniger Begriff, btw, kann dies nicht genug betonen.) Related:

    Fügte ich demos auf der Geige zu zeigen, die teurer query plan:

    dbfiddle hier

    Verwandte:

    InformationsquelleAutor der Antwort Erwin Brandstetter

Schreibe einen Kommentar