Génération de séquences de dates PostgreSQL et generate_series

Une fois à l'un des projets, nous devions établir un rapport sur les transactions financières de la période avec un groupe de sous-totaux à la fin du mois.

La tâche est généralement simple, déterminer les périodes requises dans un grand intervalle, lier chaque opération à une période appropriée, grouper et additionner la somme.

Pour générer des périodes dans l'intervalle, je prenais habituellement la fonction generate_series, que j'utilise souvent pour générer des séquences numériques. J'ai vérifié la documentation sur la possibilité de générer une séquence de dates, j'ai considéré un exemple, j'ai écrit une requête et j'ai été perplexe.

select gs::date from generate_series('2018-01-31', '2018-05-31', interval '1 month') as gs; 

Le résultat était aussi inattendu que logique. La fonction generate_series a honnêtement généré de manière itérative une séquence de dates basée sur le principe de l'ajout séquentiel d'un décalage à la valeur précédente. En même temps, à chaque étape, l'exactitude et l'édition de la date reçue ont été vérifiées. Le 31 février ne se produit pas, donc la date a été changée au 28 février et l'ajout supplémentaire du mois a ramené toute la séquence au 28.

UPD Explications après les questions dans les commentaires. En général, la tâche initiale est plus large - regrouper les données sur des jours arbitraires du mois. Par exemple, regroupez le 20e jour de chaque mois, le 15e jour, mais il n'y a aucun problème avec ces dates lors de la génération. Le mécanisme que nous recherchons devrait tout aussi bien construire une séquence de 10 numéros de chaque mois, 21 numéros et bien calculer les fins de mois.

Je me demande comment l'opération d'addition se déroulera avec plusieurs mois à la fois? Que se passera-t-il si nous ajoutons l'intervalle non de manière itérative, mais "en vrac"?

 select '2018-01-31'::date +interval '1 mons' 28.02.2018 select '2018-01-31'::date +interval '2 mons' 31.03.2018 

Dans ce cas, l'ajout est fait honnêtement.
Comment générer les dates nécessaires en utilisant cette approche?

Si le nombre de mois est connu, alors c'est très simple:

 select '2018-01-31'::date +make_interval(0, i) as gs from generate_series(0, 4, 1) as i 

Que faire si seules la date de début et la date de fin sont connues?
Ce problème peut être tout simplement résolu en écrivant une fonction stockée et un simple cycle dedans, cependant, nous sommes intéressés par une option d'implémentation lorsqu'il n'y a aucune possibilité ou désir de boucher la structure de la base de données avec des objets inutiles.
Essayons de réduire la tâche à la précédente.

Le code suivant est dans une certaine mesure une maquette et ne prétend pas être élégant; nous écrivons les premières options de requête dans l'entreprise en mettant l'accent sur la flexibilité et l'interchangeabilité des blocs

 /*  -  ,         ,      */ with dates as ( select '2018-01-31'::date as dt1, '2018-05-31'::date as dt2 ), /*      ""  */ g_age as ( select age( (select dt2 from dates), (select dt1 from dates)) ), /*       (*12 + )   +1       */ months as ( select (extract(year from (select * from g_age))*12 + extract(month from (select * from g_age))+1)::integer ), /*  ,           -   ,       */ seq as( select ((select dt1 from dates) + make_interval(0, gs)) as gs from generate_series ( 0, (select * from months), 1 ) as gs where ((select dt1 from dates) + make_interval(0, gs)) <= (select dt2 from dates) ) /*         */ select * from seq 

La solution s'est avérée assez lourde, mais fonctionnante et il est assez simple de l'intégrer dans d'autres requêtes via le mécanisme with.
Nous avons mis en œuvre le rapport, mais l'idée que cette demande est non seulement lourde, mais également limitée dans son utilisation uniquement par étapes sur des mois entiers, n'a pas donné de répit.

Option 2
Après un certain temps, j'ai réalisé que la génération de date séquentielle est essentiellement une procédure récursive. Seulement pas dans sa forme pure, car dans notre cas, le calcul de la prochaine date à partir de la précédente conduit au problème d'origine. Mais à chaque étape, nous pouvons augmenter l'intervalle ajouté au début de notre période:

 /*    -,     timestamp */ with recursive dates as ( select '2018-01-31'::timestamp as dt1, '2018-05-31'::timestamp as dt2, interval '1 month' as interval ), /*           ,          ,   .  ,        */ pr AS( select 1 as i, (select dt1 from dates) as dt union select i+1 as i, ( (select dt1 from dates) + ( select interval from dates)*i)::timestamp as dt from pr where ( ((select dt1 from dates) + (select interval from dates)*i)::timestamp) <=(select dt2 from dates) ) select dt as gs from pr; 

Cette requête fonctionne correctement avec toutes les périodes et intervalles d'entrée.