CODEWITHSUNDEEP
postgresqlpostgresql-9.1
Tomas Greif
Tomas Greif

Reputation: 22663

Generate series of quarters in Postgresql

I need to generate series of quarters, given start date and end date. I know of generate_series(), but it just does not work with quarter:

SELECT * FROM generate_series('2008-01-01 00:00'::timestamp,'2009-01-01 12:00', '1 quarter');

What makes quarter so special? Otherwise generate_series() works with pretty much everything, from microseconds to millenium:

select * from generate_series('2008-01-01 00:00'::timestamp,'2008-01-01 00:00:00.001', '1 microsecond');
select * from generate_series('2008-01-01 00:00'::timestamp,'2008-01-01 00:01', '1 second');
select * from generate_series('2008-01-01 00:00'::timestamp,'2008-01-01 01:00', '1 minute');
select * from generate_series('2008-01-01 00:00'::timestamp,'2008-01-01 12:00', '1 hour');
select * from generate_series('2008-01-01 00:00'::timestamp,'2009-01-01 12:00', '1 day');
select * from generate_series('2008-01-01 00:00'::timestamp,'2009-01-01 12:00', '1 week');
select * from generate_series('2008-01-01 00:00'::timestamp,'2009-01-01 12:00', '1 month');
select * from generate_series('2008-01-01 00:00'::timestamp,'2009-01-01 12:00', '1 year');
select * from generate_series('2008-01-01 00:00'::timestamp,'2009-01-01 12:00', '1 decade');
select * from generate_series('2008-01-01 00:00'::timestamp,'2009-01-01 12:00', '1 century');
select * from generate_series('2008-01-01 00:00'::timestamp,'2009-01-01 12:00', '1 millennium');

If quarter cannot be used then what is the best other option? For now, I have:

select 
   date_trunc('quarter',generate_series) gs 
from 
   generate_series('2008-01-01 00:00'::timestamp,'2009-01-01 12:00', '1 month') 
group by 
   gs
order by
   gs;

Upvotes: 8

Views: 4303

Answers (1)

Daniel Vérité
Daniel Vérité

Reputation: 61626

Shouldn't this do what you expect:

SELECT * FROM generate_series('2008-01-01 00:00'::timestamp,
                              '2009-01-01 12:00', '3 months');

Result:

   generate_series   
---------------------
 2008-01-01 00:00:00
 2008-04-01 00:00:00
 2008-07-01 00:00:00
 2008-10-01 00:00:00
 2009-01-01 00:00:00
(5 rows)

Upvotes: 18

Related Questions