Génération de séries temporelles entre deux dates dans PostgreSQL

Question

J'ai une requête comme celle-ci qui génère une série de dates entre deux dates données :

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

Il génère 162 dates entre 2004-03-07 y 2004-08-16 et c'est ce que je veux. Le problème avec ce code est qu'il ne donne pas la bonne réponse lorsque les deux dates sont d'années différentes, par exemple lorsque j'essaye 2007-02-01 y 2008-04-01 .

Y a-t-il une meilleure solution ?

Answer 1

Peut être fait sans conversion vers/depuis int (mais vers/depuis timestamp à la place)

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

Answer 2

Pour générer une série de dates c'est le optimal manière :

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

• Supplémentaire date_trunc() n'est pas nécessaire. La fonte à date ( day::date ) le fait implicitement.

• Mais il n'y a pas non plus d'intérêt à couler les littéraux de date en date comme paramètre d'entrée. Au contraire, timestamp est le meilleur choix . L'avantage en termes de performances est faible, mais il n'y a aucune raison de ne pas le prendre. De plus, vous ne faites pas intervenir inutilement les règles de l'heure d'été (DST) couplées à la conversion de l'heure d'hiver à l'heure d'été. date à timestamp with time zone et retour. Voir ci-dessous.

Syntaxe courte équivalente, moins explicite :

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

Ou avec la fonction de retour d'un ensemble dans le programme SELECT liste :

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

El AS Le mot clé est requis dans la dernière variante, Postgres interpréterait mal l'alias de la colonne day autrement. Et je voudrais pas Cette variante n'était pas conseillée avant Postgres 10, du moins pas avec plus d'une fonction de retour d'ensemble dans le même fichier de données. SELECT liste :

• Quel est le comportement attendu pour les fonctions à retour d'ensemble multiples dans la clause SELECT ?

(Cela mis à part, la dernière variante est généralement la plus rapide d'une marge minime).

Pourquoi timestamp [without time zone] ?

Il existe un certain nombre de variantes surchargées de generate_series() . Actuellement (Postgres 11) :

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**

( numeric Les variantes ont été ajoutées avec Postgres 9.5). Les plus importantes sont les deux dernières en gras prendre et rendre timestamp / timestamptz .

Il y a pas de prise ou de restitution de variante date . Un cast explicite est nécessaire pour retourner date . L'appel avec timestamp se résout directement en la meilleure variante sans descendre dans les règles de résolution des types de fonctions et sans cast supplémentaire pour l'entrée.

timestamp '2004-03-07' est parfaitement valable, en fait. La partie temps omise est remplacée par défaut par 00:00 avec le format ISO.

Merci à fonction type résolution nous pouvons encore passer date . Mais cela demande plus de travail à Postgres. Il existe un implicite moulage de date à timestamp ainsi qu'un autre de date à timestamptz . Serait ambiguë, mais timestamptz es "préféré" parmi les "types de date/heure". Ainsi, le Le match est décidé à l'étape 4d. :

Passez en revue tous les candidats et gardez ceux qui acceptent les types préférés (de la catégorie de type du type de données d'entrée) dans la plupart des positions où une conversion de type sera nécessaire. Gardez tous les candidats si aucun n'accepte les les types préférés. S'il ne reste qu'un seul candidat, utilisez-le, sinon passez à l'étape suivante. à l'étape suivante.

En plus du travail supplémentaire dans la résolution du type de fonction, ceci ajoute un cast supplémentaire à la fonction timestamptz - ce qui non seulement ajoute un coût supplémentaire, mais peut également introduire des problèmes avec le DST, conduisant à des résultats inattendus dans de rares cas. (Le DST est un concept stupide, on ne le dira jamais assez) :

• Comment générer une série de dates dans PostgreSQL ?
• Comment générer une série chronologique dans PostgreSQL ?

J'ai ajouté des démonstrations au fiddle montrant le plan de requête le plus coûteux :

_db<>fidèle aquí_

En rapport :

• Existe-t-il un moyen de désactiver la surcharge des fonctions dans Postgres ?
• Générer des séries de dates - en utilisant le type de date comme entrée
• Distribution des types de données Postgres

Answer 3

Vous pouvez générer des séries directement avec des dates. Il n'est pas nécessaire d'utiliser des ints ou des timestamps :

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

Answer 4

Vous pouvez également utiliser ceci.

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