Can I start the quarter from the month I wanted to on PostgreSQL?-CodePudding

I am using PostgreSQL 14.5

I can classify the year by quarter using the Postgres keyword QUARTER as illustrated in the documentation just like SELECT EXTRACT(QUARTER FROM TIMESTAMP '2001-02-16 20:38:40');. And it works. The default quarter starts with January. Which makes complete sense. I.e, quarter one is composed of January, February, and March. The others follow then. But, I wanted to make the month configurable so that the quarter can start at any month.

Eg. If the month starts in April, April, May, and July would be the first quarter.

I have started writing my custom function to come up with a solution but,

Can I do this without creating a custom function?

CodePudding user response：

Hers is it:

SELECT date_trunc('quarter', now() - interval '6 month') AS quarter

CodePudding user response：

You can use simple condition statement to get it done as below:

Your query:

 SELECT EXTRACT(QUARTER FROM TIMESTAMP '2001-02-16 20:38:40');

extract
1

Solution Query (quarter starts with April):

select   Case
         when extract(month from TIMESTAMP '2001-02-16 20:38:40') in (4,5,6) then 'Q1'
         when extract(month from TIMESTAMP '2001-02-16 20:38:40') in (7,8,9) then 'Q2'
         when extract(month from TIMESTAMP '2001-02-16 20:38:40') in (10,11,12) then 'Q3'
         when extract(month from TIMESTAMP '2001-02-16 20:38:40') in (1,2,3) then 'Q4'
         End as Quarter

quarter
Q4

For Quarter starting with July:

select   Case
         when extract(month from TIMESTAMP '2001-02-16 20:38:40') in (7,8,9) then 'Q1'
         when extract(month from TIMESTAMP '2001-02-16 20:38:40') in (10,11,12) then 'Q2'
         when extract(month from TIMESTAMP '2001-02-16 20:38:40') in (1,2,3) then 'Q3'
         when extract(month from TIMESTAMP '2001-02-16 20:38:40') in (4,5,6) then 'Q4'
         End as Quarter

fiddle

You can also use interval to change the actual date like @nikhil sugandh suggested.

If you want April to be the starting month then you need to subtract 3 from your actual date. If the month is July then you need to subtract 6.

select extract(quarter from TIMESTAMP '2001-02-16 20:38:40'-interval '3 Month')

extract
4

fiddle