I have the following table:

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

CodePudding user response：

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

CodePudding user response：

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

CodePudding user response：

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

SELECT EXTRACT(quarter from '30/12/2022'::date)