Generación de secuencia de fecha PostgreSQL y generate_series

Una vez en uno de los proyectos, necesitábamos elaborar un informe sobre las transacciones financieras para el período con un grupo de subtotales al final del mes.

La tarea es generalmente simple, determinar los períodos requeridos dentro de un intervalo grande, adjuntar cada operación a un período adecuado, agrupar y sumar la cantidad.

Para generar períodos dentro del intervalo, habitualmente tomé la función generate_series, que a menudo uso para generar secuencias numéricas. Revisé la documentación sobre la posibilidad de generar una secuencia de fechas, consideré un ejemplo, escribí una consulta y me quedé perplejo.

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

El resultado fue tan inesperado como lógico. La función generate_series genera honestamente de forma iterativa una secuencia de fechas basada en el principio de agregar secuencialmente un cambio al valor anterior. Al mismo tiempo, en cada paso, se verificó la corrección y edición de la fecha recibida. El 31 de febrero no sucede, por lo que la fecha se transformó al 28 de febrero y la adición adicional del mes redujo toda la secuencia al 28.

UPD Explicaciones después de las preguntas en los comentarios. En general, la tarea inicial es más amplia: agrupar datos en días arbitrarios del mes. Por ejemplo, agrupe por el día 20 de cada mes, por el día 15, pero no hay problemas con tales fechas al generar. El mecanismo que estamos buscando debería igualmente construir una secuencia de 10 números de cada mes, 21 números y resolver correctamente los fines de los meses.

Me pregunto cómo se comportará la operación de adición dentro de varios meses a la vez. ¿Qué sucederá si agregamos el intervalo no de forma iterativa, sino "a granel"?

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

En este caso, la adición se realiza con honestidad.
¿Cómo generar las fechas necesarias utilizando este enfoque?

Si se conoce la cantidad de meses, entonces es muy simple:

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

¿Qué sucede si solo se conocen la fecha de inicio y la fecha de finalización?
Este problema puede resolverse simplemente escribiendo una función almacenada y un ciclo simple en ella, sin embargo, estamos interesados ​​en una opción de implementación cuando no hay posibilidad o deseo de obstruir la estructura de la base de datos con objetos innecesarios.
Intentemos reducir la tarea a la anterior.

El siguiente código es, en cierta medida, una placa de pruebas y no pretende ser elegante; escribimos las primeras opciones de consulta en la empresa con énfasis en la flexibilidad e intercambiabilidad de los bloques.

 /*  -  ,         ,      */ 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 solución resultó ser bastante engorrosa, pero funciona y es bastante simple integrarla en otras solicitudes a través del mecanismo.
Hemos implementado el informe, pero la idea de que esta solicitud no solo es engorrosa, sino que también está limitada en su uso solo por pasos durante meses completos, no dio descanso.

Opción 2
Después de un tiempo, me di cuenta de que la generación de fechas secuenciales es esencialmente un procedimiento recursivo. Solo que no en su forma pura, ya que en nuestro caso, el cálculo de la próxima fecha a partir de la anterior conduce al problema original. Pero en cada paso podemos aumentar el intervalo agregado al comienzo de nuestro período:

 /*    -,     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; 

Esta consulta funciona correctamente con cualquier período e intervalo de entrada.