How to find the previous quarter value based on current quarter in PostgreSQL?

Question

I need to find the value of the previous quarter for a particular given date. Like for example, I passed a date as on 12th January, 2015, I need the result to be as 4th quarter of 2014.

If I use the below query, I get the result as 0

SELECT EXTRACT(quarter FROM '2015-01-01'::timestamp) - 1;

Result : 0

Can someone let me know how do I get me desired result as mentioned above?

Answer 1

You need to "trunc" the date to the beginning of its quarter, the subtract a day, and then get the quarter for that date:

extract(quarter from date_trunc('quarter', date '2015-01-01')::date - 1)

If you want to get the year and the quarter you need to use to_char() instead:

to_char(date_trunc('quarter', date '2015-01-01')::date - 1, 'yyyy-q');