Postgres date_trunc quarter with a custom start month I'm trying to create quarterly average for player scores, however the default behaviour of postgres date_trunc('quarter', source) is that it starts first quarter with YYYY-01-01.
Is there any way possible or workaround I can do to say that the first month of first quarter is for instance September? So instead of the traditional: Q1: 1-3, Q2: 4-6, Q3: 7-9, Q4: 10-12
I want to be able to specify which month is the start of Q1, so if I say September it should become: Q1: 9-11, Q2: 12-2, Q3: 3-5, Q4: 6-8
Here is how I make a standard quarterly score average with default quarter.
SELECT id,
       name,
       date_trunc('quarter', date) AS date,
       AVG(rank) AS rank,
       AVG(score) as score,
       country,
       device
FROM player_daily_score
GROUP BY id, name, 3, country, device
ORDER BY 3 desc;

I'm open for all suggestions to make this work.
If you don't really need the start date of the quarter, you can easily do this with a CASE expression:
SELECT id,
       name,
       case 
         when extract(month from date) in (9,10,11) then 'Q1'
         when extract(month from date) in (12,1,2) then 'Q2'
         when extract(month from date) in (3,4,5) then 'Q3'
         when extract(month from date) in (6,7,8) then 'Q4'
       end as quarter,
       AVG(rank) AS rank,
       AVG(score) as score,
       country,
       device
FROM player_daily_score
GROUP BY id, name, 3, country, device
ORDER BY 3 desc;

Of course you can put that into a stored function to make your queries simpler.