Ottenere l’elenco delle date in un intervallo in PostgreSQL

Mi piacerebbe ottenere l’elenco dei giorni tra le due date (includendole) in un database PostgreSQL . Ad esempio, se avessi:

  • data di inizio: 29 giugno 2012
  • data di fine: 3 luglio 2012

allora il risultato dovrebbe essere:

29 june 2012 30 june 2012 1 july 2012 2 july 2012 3 july 2012 

Quale sarebbe il modo migliore per farlo in PostgreSQL?

Grazie.

 select CURRENT_DATE + i from generate_series(date '2012-06-29'- CURRENT_DATE, date '2012-07-03' - CURRENT_DATE ) i 

o ancora più breve:

 select i::date from generate_series('2012-06-29', '2012-07-03', '1 day'::interval) i 

Come timestamp :

 select generate_series('2012-06-29', '2012-07-03', '1 day'::interval); generate_series ------------------------ 2012-06-29 00:00:00-03 2012-06-30 00:00:00-03 2012-07-01 00:00:00-03 2012-07-02 00:00:00-03 2012-07-03 00:00:00-03 

o fuso fino ad date :

 select (generate_series('2012-06-29', '2012-07-03', '1 day'::interval))::date; generate_series ----------------- 2012-06-29 2012-06-30 2012-07-01 2012-07-02 2012-07-03 

Questo dovrebbe farlo:

 select date '2012-06-29' + i from generate_series(1, (select date '2012-07-3' - date '2012-06-29')) i 

Se non vuoi ripetere lo start_date nella sottoselezione le cose diventano un po ‘più complicate:

 with min_max (start_date, end_date) as ( values (date '2012-06-29', date '2012-07-3') ), date_range as ( select end_date - start_date as duration from min_max ) select start_date + i from min_max cross join generate_series(1, (select duration from date_range)) i; 

(Vedi la risposta di maniek per una versione molto migliore del problema “no-repeat”)

Per cose come questa è generalmente utile avere una tabella delle date nel sistema.

Proprio come una tabella di numeri, possono essere molto utili e più veloci da utilizzare rispetto alla generazione di date al volo, soprattutto quando si esegue il ridimensionamento su insiemi di dati di grandi dimensioni.

Tale tabella di date dal 1900 al 2100 sarà molto piccola, quindi non c’è molto spazio in archivio.

Modifica: non so perché questo viene votato, probabilmente sarà il migliore per le prestazioni. Inoltre ha tanti altri vantaggi. Vuoi colbind gli ordini a un numero di prestazioni di un trimestre? È un semplice collegamento tra i tavoli. (Order.OrderDate -> Date.Date -> Date.Quarter -> PerformanceTotal.Quarter) ecc. È lo stesso per i giorni lavorativi, come l’ultimo giorno lavorativo di un mese o il primo martedì del mese precedente. Come una tabella di numeri, li raccomando vivamente!

 select generate_series('2012-06-29', '2012-07-03', '1 day'::interval)::date; 

Se hai già un database che vuoi interrogare:

 SELECT TO_CHAR(date_column,'DD Mon YYYY') FROM some_table WHERE date_column BETWEEN '29 Jun 2012' AND '3 JUL 2012' GROUP BY date_column ORDER BY date_column 

Ciò comporterà:

 "29 Jun 2012" "30 Jun 2012" "01 Jul 2012" "02 Jul 2012" "03 Jul 2012" 

Se l’intervallo di date deve provenire da un’espressione di tabella, è ansible utilizzare il seguente costrutto:

 DROP TABLE tbl ; CREATE TABLE tbl (zdate date NOT NULL ); INSERT INTO tbl(zdate) VALUES( '2012-07-01') , ('2012-07-09' ); WITH mima AS ( SELECT MIN(zdate)::timestamp as mi , MAX(zdate)::timestamp as ma FROM tbl ) SELECT generate_series( mima.mi, mima.ma, '1 day':: interval):: date FROM mima ; 

I cast sono necessari perché generate_series () non accetta argomenti di data.

Questa funzione PLpg / SQL farebbe il trucco:

 CREATE OR REPLACE FUNCTION getDateList(date1 date, date2 date) RETURNS SETOF date AS $BODY$ DECLARE count integer; lower_limit integer := 0; upper_limit integer := date2 - date1; BEGIN FOR count IN lower_limit..upper_limit LOOP RETURN NEXT date1 + count; END LOOP; RETURN; END; $BODY$ LANGUAGE plpgsql VOLATILE