0

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?

Improve this question
1
  • Replace starting date literal ('2018-1-1'::date) with proper expression which calculates starting date you need dependent by something (current date, for example). Commented Oct 9, 2018 at 6:33

1 Answer 1

Reset to default
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?

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.