Tijdreeksen genereren tussen twee datums in PostgreSQL

Ik heb een zoekopdracht als deze die mooi een reeks datums genereert tussen 2 gegeven datums:

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

Het genereert 162 datums tussen 2004-03-07en 2004-08-16en dit is wat ik wil. Het probleem met deze code is dat hij niet het juiste antwoord geeft als de twee datums uit verschillende jaren komen, bijvoorbeeld als ik 2007-02-01en 2008-04-01.

Is er een betere oplossing?

Antwoord 1, autoriteit 100%

Kan worden gedaan zonder conversie van/naar int (maar in plaats daarvan naar/van tijdstempel)

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

Antwoord 2, autoriteit 43%

Om een ​​reeks datumste genereren, is dit de optimalemanier:

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

• Aanvullende date_trunc()is niet nodig. De cast tot date(day::date) doet dat impliciet.

• Maar het heeft ook geen zin om letterlijke datums te casten naar dateals invoerparameter. Au contraire, timestampis de beste keuze. Het prestatievoordeel is klein, maar er is geen reden om het niet te nemen. En je maakt niet onnodig gebruik van zomertijdregels in combinatie met de conversie van datenaar timestamp with time zoneen terug. Zie hieronder.

Equivalente, minder expliciete korte syntaxis:

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

Of met de set-return-functie in de SELECTlijst:

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

Het trefwoord ASis vereistin de laatste variant, anders zou Postgres de kolomalias dayverkeerd interpreteren. En ik zou die variant nietadviseren vóór Postgres 10 – in ieder geval niet met meer dan één set-retourfunctie in dezelfde SELECTlijst:

• Wat is het verwachte gedrag voor functies die meerdere sets retourneren in de SELECT-component?

(Afgezien daarvan is de laatste variant meestal met een kleine marge het snelst.)

Waarom timestamp [without time zone]?

Er zijn een aantal overbelaste varianten van generate_series(). Momenteel (Postgres 11):

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

function_signature | return_type
:------------------------------------------------- ------------------------------- | :--------------------------
genereerreeks (geheel getal, geheel getal, geheel getal) | geheel getal
genereerreeks (geheel getal, geheel getal) | geheel getal 
genereerreeks(bigint,bigint,bigint) | bigint
genereer_series(bigint,bigint) | bigint
genereerreeks (numeriek, numeriek, numeriek) | numeriek
genereerreeks (numeriek, numeriek) | numeriek
generate_series(tijdstempel zonder tijdzone,tijdstempel zonder tijdzone,interval) | tijdstempel zonder tijdzone
genereerreeks (tijdstempel met tijdzone, tijdstempel met tijdzone, interval) | tijdstempel met tijdzone

(numericvarianten zijn toegevoegd met Postgres 9.5.) De relevante zijn de laatste twee vetgedruktdie timestamp/ timestamptz.

Er is geen variant die dateneemt of retourneert. Een expliciete cast is nodig om datete retourneren. De aanroep met timestamp-argumenten wordt direct omgezet in de beste variant zonder af te dalen in resolutieregels voor functietypes en zonder extra cast voor de invoer.

timestamp '2004-03-07'is perfect geldig, btw. Het weggelaten tijdsgedeelte is standaard 00:00met ISO-formaat.

Dankzij resolutie functietypewe kunnen nog steeds datedoorgeven. Maar dat vereist meer werk van Postgres. Er is een implicietecastvan datetot timestampen ook een van datenaar timestamptz. Zou dubbelzinnig zijn, maar timestamptzis “voorkeur”onder de “datum/tijd-types”. Dus de overeenkomst wordt beslist bij stap 4d.:

Overloop alle kandidaten en bewaar degenen die voorkeurstypen accepteren
(van de typecategorie van het invoergegevenstype) op de meeste plaatsen waar
typeconversie is vereist. Alle kandidaten behouden als geen enkele accepteert
voorkeurstypes. Als er nog maar één kandidaat over is, gebruik die dan; anders doorgaan
naar de volgende stap.

Naast het extra werk in het oplossen van functietypes voegt dit een extra cast toe aan timestamptz– wat niet alleen meer kosten met zich meebrengt, het kan ook problemen met DST introduceren, wat in zeldzame gevallen tot onverwachte resultaten kan leiden. (DST is trouwens een debiel concept, ik kan dit niet genoeg benadrukken.) Gerelateerd:

• Hoe genereer ik een datumreeks in PostgreSQL?
• Hoe genereer ik een tijdreeks in PostgreSQL?

Ik heb demo’s toegevoegd aan de viool met het duurdere zoekplan:

db<>fiddle hier

Gerelateerd:

• Is er een manier om overbelasting van functies uitschakelen in Postgres
• Genereer een reeks datums – met behulp van datum typ als invoer
• Gegoten Postgres-gegevenstype

Antwoord 3, autoriteit 17%

Je kunt direct series met datums genereren. Geen noodzaak om ints of tijdstempels te gebruiken:

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

Antwoord 4

Je kunt dit ook gebruiken.

select generate_series  ( '2012-12-31'::timestamp , '2018-10-31'::timestamp , '1 day'::interval) :: date