In many SQL statements, there is a need to generate “Date Series” and then run some left join statements in order to build the required report. Let’s see how we can generate Date Series in PostgreSQL. In this example, we will generate monthly date series, going back two years ago up to now.
SELECT generate_series((date_trunc('year', current_date) - interval '2 year'), current_date, '1 month')::date AS gdate
order by 1
You can easily change the parameters insider the generate_series function in order to generate series daily or annual series and so on. You can also change the frequency. Let’s say that we want date series every month months.
SELECT generate_series((date_trunc('year', current_date) - interval '2 year'), current_date, '4 month')::date AS gdate
order by 1
