Postgresql – Postgres date_trunc quarter with a custom start month

postgresqlstatistics

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.

Best Answer

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.

Related Solutions

PostgreSQL custom domain with trigger

You cannot do that.

As the documentation says:

CREATE TRIGGER creates a new trigger. The trigger will be associated with the specified table or view

This clearly excludes types and domains. (Very logically: triggers are normally fired upon changing something - here you want to change a column, not the type.)

You should instead use a stored procedure to take care of your password column(s). And personally I think the less places password are stored at, the better.

Postgresql – Custom Postgres function not working

Peter's quite right about the problem, of course. However, this entire function is unnecessarily slow and complex - that repeated string concatenation will be horrible for performance, and PL/PgSQL loops are best avoided when you can anyway.

You can do the same job with an ordinary SQL function using generate_subscripts:

CREATE OR REPLACE FUNCTION intarr_to_hex_string(integer[]) RETURNS text AS $$
SELECT 
  string_agg(
    lpad(to_hex($1[x]),2,'0'),
    ''
    order by x
  ) 
FROM generate_subscripts($1,1) x;
$$ LANGUAGE sql;

The above version expects an integer array, but you can accept text arrays with an overload:

CREATE OR REPLACE FUNCTION intarr_to_hex_string(text[]) RETURNS text AS $$
SELECT intarr_to_hex_string($1::integer[])
$$ LANGUAGE sql;

Note that no sanity check is done to make sure the hex string is two digits or less. You should really add a check where you use a CASE statement to execute a PL/PgSQL function that raises an error if there's bad input. Here's a complete example:

CREATE OR REPLACE FUNCTION raise_exception(text)
  RETURNS void LANGUAGE plpgsql AS
$BODY$ 
BEGIN 
   RAISE EXCEPTION '%', $1; 
END; 
$BODY$;

CREATE OR REPLACE FUNCTION intarr_to_hex_string(integer[]) RETURNS text AS $$
SELECT 
  string_agg(lpad(
    CASE WHEN $1[x] < 256 THEN
      to_hex($1[x])
    ELSE
      raise_exception('Argument to intarr_to_hex_string contained value '||coalesce($1[x]::text,'NULL')||', expected all values in i[] to be 0 <= i < 256. Full argument was '||quote_literal($1)||'.')::text
    END,
    2, '0'),
  ''
  order by x) 
FROM generate_subscripts($1,1) x;
$$ LANGUAGE sql;

Best Answer

Related Solutions

PostgreSQL custom domain with trigger

Postgresql – Custom Postgres function not working

Related Question