postgresqldatedate-math

Return number of days passed in current quarter

How can I get the number of days passed in the current quarter?

For example, if today is 1/2/2021, it will return 2.
If today is 2/5, it will return 36.
If today is 4/2, it will return 2.

Solution

  • Use date_trunc() to get the start of the quarter and subtract dates:

    WITH cte(day) AS (
   VALUES 
     (date '2021-01-02')
   , (date '2021-02-05')
   , (date '2021-04-02')
   )
SELECT day
     , day - date_trunc('quarter', day)::date + 1 AS days_passed_in_quarter
FROM   cte;

    day     | days_passed_in_quarter 
------------+------------------------
 2021-01-02 |                      2
 2021-02-05 |                     36
 2021-04-02 |                      2

    + 1 to fix off-by-one error as you clearly want to include the current day as "passed".

    Always use unambiguous ISO 8601 date format (YYYY-MM-DD - 2021-02-05), which is the default in Postgres and always unambiguous, or you depend on the current datestyle setting (and may be in for surprises). Also avoids misunderstandings general communication.

    Related: