Pembuatan urutan tanggal PostgreSQL dan generate_series

Sekali di salah satu proyek kami perlu membuat laporan tentang transaksi keuangan untuk periode tersebut dengan sekelompok subtotal pada akhir bulan.

Tugas umumnya sederhana, menentukan periode yang diperlukan dalam interval besar, melampirkan setiap operasi ke periode yang sesuai, mengelompokkan dan menjumlahkan jumlahnya.

Untuk menghasilkan periode dalam interval, saya biasanya menggunakan fungsi generate_series, yang sering saya gunakan untuk menghasilkan urutan numerik. Saya memeriksa dokumentasi tentang kemungkinan menghasilkan urutan tanggal, dianggap sebagai contoh, menulis kueri dan bingung.

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

Hasilnya tak terduga seperti logis. Fungsi generate_series secara jujur ​​secara iteratif menghasilkan urutan tanggal berdasarkan pada prinsip menambahkan perubahan secara berurutan ke nilai sebelumnya. Pada saat yang sama, pada setiap langkah, kebenaran dan pengeditan tanggal yang diterima diperiksa. 31 Februari tidak terjadi, jadi tanggalnya diubah menjadi 28 Februari dan penambahan lebih lanjut pada bulan itu membawa seluruh urutan ke tanggal 28.

UPD Penjelasan demi pertanyaan di komentar. Secara umum, tugas awal lebih luas - untuk mengelompokkan data pada hari-hari sewenang-wenang dalam sebulan. Misalnya, kelompokkan pada hari ke-20 setiap bulan, pada hari ke-15, tetapi tidak ada masalah dengan tanggal tersebut saat menghasilkan. Mekanisme yang kita cari seharusnya sama-sama membangun urutan 10 angka setiap bulan, 21 angka dan menghitung akhir bulan dengan benar.

Saya ingin tahu bagaimana operasi penambahan akan berperilaku dengan beberapa bulan sekaligus? Apa yang akan terjadi jika kita menambahkan interval bukan secara iteratif, tetapi "secara massal"?

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

Dalam hal ini, penambahan dilakukan dengan jujur.
Bagaimana cara menghasilkan tanggal yang diperlukan menggunakan pendekatan ini?

Jika jumlah bulan diketahui, maka sangat sederhana:

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

Bagaimana jika hanya tanggal mulai dan tanggal akhir yang diketahui?
Masalah ini dapat dengan mudah diselesaikan dengan menulis fungsi tersimpan dan siklus sederhana di dalamnya, namun, kami tertarik pada opsi implementasi ketika tidak ada kemungkinan atau keinginan untuk menyumbat struktur database dengan objek yang tidak perlu.
Mari kita coba mengurangi tugas ke yang sebelumnya.

Kode berikut sampai batas tertentu merupakan papan tempat memotong roti dan tidak berpura-pura anggun; kami menulis opsi permintaan pertama di perusahaan dengan penekanan pada fleksibilitas dan pertukaran dari blok.

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

Solusinya ternyata agak rumit, tetapi bekerja dan cukup sederhana untuk mengintegrasikannya ke dalam permintaan lain melalui mekanisme with.
Kami telah mengimplementasikan laporan, tetapi gagasan bahwa permintaan ini tidak hanya rumit, tetapi juga terbatas dalam penggunaannya hanya dengan langkah-langkah selama sebulan penuh, tidak memberikan istirahat.

Opsi 2
Setelah beberapa saat, saya sadar bahwa pembuatan tanggal berurutan pada dasarnya adalah prosedur rekursif. Hanya saja tidak dalam bentuk murni, karena dalam kasus kami, perhitungan tanggal berikutnya dari yang sebelumnya mengarah ke masalah asli. Tetapi pada setiap langkah kita dapat meningkatkan interval yang ditambahkan ke awal periode kita:

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

Kueri ini berfungsi dengan benar dengan periode dan interval waktu input apa pun.