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

Postgresql – Select Current Quarter From Quarter Dates table

I have the following table:

Quarter Start
30/12/2016
31/03/2017
30/06/2017
29/09/2017
29/12/2017
30/03/2018
29/06/2018
28/09/2018
28/12/2018
29/03/2019
28/06/2019
27/09/2019
27/12/2019
27/03/2020
26/06/2020
25/09/2020
01/01/2021
02/04/2021
02/07/2021
01/10/2021
31/12/2021
01/04/2022
01/07/2022
30/09/2022
30/12/2022
24/03/2023
30/06/2023
29/09/2023
29/12/2023
29/03/2024
28/06/2024
27/09/2024
03/01/2025
04/04/2025
03/10/2025
02/01/2026
03/04/2026
03/07/2026
02/10/2026
01/01/2027
02/04/2027
02/07/2027
01/10/2027

What can I write to always select the current quarter according to this table and the current date? For eg the current quarter is ’09/12/2022′, how can I always ensure to select the current quarter?

Many thanks

UPDATE: I have run by some of the suggestions as follows

SELECT 
    quarter_start,
    EXTRACT (QUARTER FROM quarter_start)
FROM
    odwh_work.sdc_2027
WHERE
    EXTRACT (QUARTER FROM quarter_start) =  EXTRACT (QUARTER FROM current_date)
ORDER BY 1 DESC

But I am still not returning 2022-12-09

Tags:

3

Answers


  1. 0

    If you don’t have many rows you can

    select * from foo where extract('quarter' from 'Quarter Start') = extract('quarter' from now());

    But beware that this can’t utilize an index on a date since quarter (1,2,3,4) is not a date in itself but a digit.

    create table stack (id serial, quarter date);
insert into stack (quarter) values ('2022-09-15');
insert into stack (quarter) values ('2022-10-10');

select * from stack;
 id |  quarter   
----+------------
  2 | 2022-09-15
  3 | 2022-10-10

select * from stack where extract('quarter' from quarter) = extract('quarter' from now());
 id |  quarter   
----+------------
  3 | 2022-10-10
    Login or Signup to reply.
  2. 0

    Not sure what you expect, but you can get the quarter by extracting it:

    SELECT EXTRACT(quarter from '30/12/2022'::date)
    Login or Signup to reply.
  3. 0

    If I understand correctly, you want to find the date which both matches the current quarter and the current year. This can be done so:

    SELECT yourdate FROM yourtable 
WHERE EXTRACT('quarter' FROM yourdate) =  EXTRACT('quarter' FROM now())
AND EXTRACT('year' FROM yourdate) = EXTRACT('year' FROM now());

    This query can of course find multiple dates when they satisfy these conditions. If you want to find only the latest of them, use MAX:

    SELECT MAX(yourdate) FROM yourtable 
WHERE EXTRACT('quarter' FROM yourdate) =  EXTRACT('quarter' FROM now())
AND EXTRACT('year' FROM yourdate) = EXTRACT('year' FROM now());

    See db<>fiddle

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

Back To Top
Search