How do I generate a date series in PostgreSQL without hard coding the dates?

Question

I want to generate a series of months. The start should be the first month of the current year, the stop is the current date with an interval of 1 month.

I used the function generate_series (see below) and it gives me the desired result, but I don't want the starting date to be hard coded. It should change depending the year it is in.

select to_char(generate_series('2018-1-1'::date,
                now()::date,
                '1 month'), 'YYYYmm') as dat 

Output:

201801
201802
201803
201804
201805
201806
201807
201808
201809
201810

Any ideas on how I can solve this?

Answer 1

I want to generate a series of months. The start should be the first month of the current year, the stop is the current date with an interval of 1 month.

I would just keep it like d::date but you can format it however you want,

SELECT d::date, to_char(d, 'YYYYmm')
FROM generate_series(
  date_trunc('year', now())::timestamp without time zone,
  now()::timestamp without time zone,
  '1 month'
) AS gs(d);

But you can reformat it with to_char() if you want.

For more information see How do I generate a date series in PostgreSQL?