Use generate_series to populate list of months for a given year in PostgreSQL

Question

I have the following query that generates list of months for given year when input is date (2014-01-01):

SELECT to_char(dd, 'Month') 
FROM generate_series(CAST('2014-01-01' AS DATE), date_trunc('month', now()), '1 month') as dd;

  to_char  
-----------
 January  
 February 
(2 rows)

Now, instead of 2014-01-01 I need to pass only 2014 and get the same result.

Houari · Accepted Answer · 2014-02-21 14:54:04Z

2

SELECT distinct to_char(dd, 'Month') FROM generate_series(to_date('2013', 'YYYY') , date_trunc('month', now()), '1 month') as dd;

edited Feb 21, 2014 at 14:54

answered Feb 21, 2014 at 12:18

Houari

5,6513 gold badges33 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Amit Sharad Over a year ago

thanks but the o/p has the additional 2 values of January and February each

Houari Over a year ago

@AmitSharad Sorry, i did not understand what you just said: thanks but the o/p has the additional 2 values of January and February each

Amit Sharad Over a year ago

If you input year as 2013 , it will return additional January ad February , please verify

Houari Over a year ago

@AmitSharad So you want to get distinct values of month ? Please update your question to be more specific.

Amit Sharad Over a year ago

@Houri Yes you're correct the distinct Month values , anyways I will accept your answer. Thank you for your comments and time on this :)

|

Tomas Greif · Accepted Answer · 2014-02-21 14:51:25Z

2

You can apped -01-01 to year input:

SELECT 
  to_char(dd, 'Month') 
FROM 
  generate_series(
      ('2013' || '-01-01')::date, 
      ('2013' || '-01-01')::date + interval '1 year' - interval '1 day', 
      '1 month') as dd;

edited Feb 21, 2014 at 14:51

answered Feb 21, 2014 at 12:33

Tomas Greif

22.9k25 gold badges114 silver badges162 bronze badges

4 Comments

Amit Sharad Over a year ago

thanks but the o/p has the additional 2 values of January and February each

Tomas Greif Over a year ago

@AmitSharad Please be more specific. My query returns two rows, you expect different number of rows?

Amit Sharad Over a year ago

If you input year as 2013 , it will return additional January ad February , please verify

Tomas Greif Over a year ago

@AmitSharad Fixed to adjust for different year.

Collectives™ on Stack Overflow

Use generate_series to populate list of months for a given year in PostgreSQL

2 Answers 2

6 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related