Generowanie szeregów czasowych pomiędzy dwoma datami w PostgreSQL

Mam takie zapytanie, które ładnie generuje serię dat pomiędzy 2 podanymi datami: 

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

Generuje 162 daty między 2004-03-07 i 2004-08-16 i tego chcę. Problem z tym kodem polega na tym, że nie daje on właściwej odpowiedzi, gdy dwie daty pochodzą z różnych lat, na przykład gdy próbuję 2007-02-01 i 2008-04-01.

Czy jest lepsze rozwiązanie?
Author: Erwin Brandstetter, 2013-01-01
Source

3 answers

Może być wykonane bez konwersji do/Z int (ale do / z timestamp zamiast) 

SELECT date_trunc('day', dd):: date
FROM generate_series
        ( '2007-02-01'::timestamp 
        , '2008-04-01'::timestamp
        , '1 day'::interval) dd
        ;
 86
Author: wildplasser,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2013-01-01 19:40:39

Możesz generować serie bezpośrednio z datami. Nie ma potrzeby używania ints ani znaczników czasu: 

select date::date 
from generate_series(
  '2004-03-07'::date,
  '2004-08-16'::date,
  '1 day'::interval
) date;
 27
Author: fbonetti,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-02-24 16:09:02

Są dwie odpowiedzi (na razie). Oba działają, ale oba są nieoptymalne. Oto trzeci: 

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

  • Nie ma potrzeby stosowania dodatkowego date_trunc(). Obsada do date (day::date) robi to bezwarunkowo.

  • Ale nie ma też sensu rzucać literałów daty na date jako parametr wejściowy. Au contraire, timestamp to najlepszy wybór tutaj. Przewaga w wydajności jest niewielka, ale nie ma powodu, aby jej nie brać. I nie musisz niepotrzebnie angażować reguł DST w połączeniu z typem danych timestamp with time zone. Zobacz Wyjaśnienie poniżej.

Krótszy odpowiednik: 

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

Lub nawet z funkcją zwracającą set na liście SELECT: 

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

Słowo kluczowe AS jest tutaj wymagane, ponieważ Alias kolumny day byłby źle zrozumiany.

Ja bym nie radził użyć ostatniej przed Postgres 10, przynajmniej nie z więcej niż jedną funkcją zwracającą set na tej samej liście SELECT. Zobacz:

Dlaczego?

Istnieje wiele przeciążonych wariantów generate_series(). Obecnie (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)                                          | integer                    
generate_series(integer,integer)                                                  | integer                    
generate_series(bigint,bigint,bigint)                                             | bigint                     
generate_series(bigint,bigint)                                                    | bigint                     
generate_series(numeric,numeric,numeric)                                          | numeric                    
generate_series(numeric,numeric)                                                  | numeric                    
generate_series(timestamp without time zone,timestamp without time zone,interval) | timestamp without time zone
generate_series(timestamp with time zone,timestamp with time zone,interval)       | timestamp with time zone

Wariant przyjmujący i zwracający numeric został dodany z Postgres 9.5. Ale jedynymi istotnymi tutaj są dwa ostatnie pogrubione biorąc i zwracając timestamp / timestamptz.

Jako widać, że nie ma żadnego wariantu biorąc lub zwracając date. Dlatego potrzebujemy wyraźnej obsady, jeśli chcemy powrócić date. Przekazywanie rozdzielczości timestamp bezpośrednio do właściwej funkcji bez konieczności przechodzenia do reguł rozdzielczości typu funkcji i bez dodatkowego oddania dla wejścia.

I timestamp '2004-03-07' jest całkowicie poprawne. Domyślna część czasu to 00:00, jeśli została pominięta.

Dzięki rozdzielczość typu funkcji nadal możemy przejść date. Ale to wymaga więcej prac od Postgres. Jest implicit cast from date to timestamp as well as from date to timestamptz. Może być niejednoznaczne, ale timestamptz jest "preferowane" wśród "typów daty/czasu". Więc mecz rozstrzygnięty jest w kroku 4d.:

Przejrzyj wszystkich kandydatów i zachowaj tych, którzy akceptują preferowane typy (z kategorii typu danych wejściowych) w większości pozycji, w których wymagana będzie konwersja typu. Zachowaj wszystkich kandydatów, jeśli żaden nie zaakceptuje preferowane typy. Jeśli zostanie tylko jeden kandydat, użyj go; w przeciwnym razie Kontynuuj do następnego kroku.

Oprócz dodatkowej pracy w rozdzielczości typu funkcji dodaje ona dodatkowy rzut do timestamptz. Obsada do timestamptz nie tylko zwiększa koszty, może również wprowadzać problemy z czasem letnim (DST), co prowadzi do nieoczekiwanych rezultatów w rzadkich przypadkach. (DST to kretyńskie pojęcie, btw, nie mogę tego wystarczająco podkreślić. Pokrewne:

Dodałem dema do fiddle, aby pokazać droższy plan zapytań:

dbfiddle tutaj

Powiązane:

 24
Author: Erwin Brandstetter,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2018-04-13 00:56:32