Генерация последовательности дат и generate_series в PostgreSQL

Однажды на одном из проектов нам понадобилось составить отчет по финансовым операциям за период с группировкой промежуточных итогов на конец месяца.

Задача в общем-то простая, определить требуемые периоды внутри большого интервала, привязать каждую операцию к подходящему периоду, сгруппировать и сложить сумму.

Для генерации периодов внутри интервала я привычно взял функцию generate_series, которую часто использую для генерации числовых последовательностей. Сверился с документацией насчет возможности генерации последовательности дат, рассмотрел пример, написал запрос и озадачился.

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

Результат оказался столь же неожиданным, как и логичным. Функция generate_series по честному итерационно сгенерировала последовательность дат по принципу последовательного прибавления сдвига к предыдущему значению. При этом на каждом шаге проверялась корректность и правка полученной даты. 31 февраля не бывает, поэтому дата преобразовалась в 28 февраля и дальнейшее прибавление месяца сбила всю последовательность на 28 число.

Интересно как поведет себя операция сложения с несколькими месяцами сразу? Что будет если мы будем прибавлять интервал не итерационно, а «оптом»?

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

В этом случае прибавление производится по честному.
Как применяя этот подход сгенерировать нужные даты?

Если известно количество месяцев, то очень просто:

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

Что делать если известны только дата начала и дата конца?
Данную задачу можно довольно просто решить написанием хранимой функции и простым циклом в ней, однако нас интересует вариант реализации когда нет возможности или желания засорять структуру БД лишними объектами.
Попробуем свести задачу к предыдущей.

Следующий код представляет собой в некоторой степени макетную плату и не претендует на изящность, первые варианты запросов мы в компании пишем с упором на гибкость и взаимозаменяемость блоков

/* Вводим что-то типа переменных, чтобы в едином месте можно было вводить входные данные, когда нет возможности использовать параметры */ 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

Решение получилось достаточно громозким, но рабочим и его достаточно просто интегрировать в другие запросы через механизм with.
Отчет мы реализовали, однако мысль что этот запрос мало того, что громоздкий, так еще и ограничен в своем использовании только шагами по целым месяцам не давал покоя.

Вариант 2.
Спустя время меня осенило, что последовательная генерация дат по сути рекурсивная процедура. Только не в чистом виде, так как в нашем случае расчет следующей даты от предыдущей приводит к первоначальной проблеме. Зато на каждом шаге мы можем увеличивать интервал, прибавляемый к началу нашего периода:

/*  Снова определяем наши псевдопараметры-псевдопеременные, расширив их тип до 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;

Данный запрос корректно работает с любыми входными временными отрезками и интервалами.