Question 114342 in PostgreSQL Question posted in
The official documentation can be found here.

Can I start the quarter of the year from the month I wanted to on PostgreSQL?

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?

Tags:

3

Answers


  1. 0

    Hers is it:

    SELECT date_trunc('quarter', now() - interval '6 month') AS quarter
    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
  3. 0

    The problem with the selected solution is that make the month configurable so that the quarter can start at any month is not possible; you have to change the query to change the starting month. The following are a couple custom functions which allow this configuration.

    create or replace function what_quarter_is(date_in date, start_mon_in integer default 1) 
  returns integer 
 language sql 
as $$
    with recursive qtrs( mq, q, m, c) as
         (select 1,1, start_mon_in, 1  
          union all 
          select mq%3+1
               , case when mq%3+1 = 1 then q+1 else q end
               , case when m = 12 then 1 else m+1 end
               , c+1 
            from qtrs
           where c<12
          )
    select q 
      from qtrs
    where m = extract('month'  from date_in) ;
$$;

    The above defines a quarter as 3 consecutive month starting with the specified month. It builds a ‘quarterly’ month calendar from that month. If an invalid month is specified the function returns null.

    The second function is a pass thru to the above. It differs in the starting month is specified as test, i.e the month name. Note is subject to local month names, but easily updated. While it is a custom function is also is a single SQL statement. That statement could then be pulled out and run standalone; you just need to pass the parameters.

    create or replace function what_quarter_is(date_in date, start_mon_in text)
  returns integer 
 language sql 
as $$
    with mons(mn, m) as
         (values ('jan',1), ('feb',2), ('mar',3)
               , ('apr',4), ('may',5), ('jun',6)
               , ('jul',7), ('aug',8), ('sep',9)
               , ('oct',10),('nov',11),('dec',12)
          )
    select what_quarter_is(date_in,m) 
      from mons
    where lower(substr(start_mon_in,1,3)) = mn; 
$$;

    Like the prior function this function returns null if an invalid month name is supplied. However, unlike the first it cannot be extracted and run standalone. (see demo containing each)

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search