Postgres date_trunc quarter with a custom start month

Question

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.

Answer 1

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.

Answer 2

You don't actually use the number of the quarter, just the timestamps. So all you need is an offset, which you subtract and then add back after truncating, which is interval '2 month' for your given example:

SELECT id, date
     , date_trunc('quarter', date - interval '2 month')
                                  + interval '2 month' AS quarter
FROM   player_daily_score;

db<>fiddle here

However, your query makes no sense. Including the id column (assuming it's the PK) in GROUP BY is a noise operation, as nothing will be aggregated. This might make sense:

SELECT date_trunc('quarter', date - interval '2 month')
                                  + interval '2 month' AS quarter
     , country
     , device
     , AVG(rank) AS avg_rank
     , AVG(score) AS avg_score
FROM   player_daily_score
GROUP  BY 1,2,3
ORDER  BY 1 desc;

If id is not the PK, your database design can profit from normalization.