Return number of days passed in current quarter

Question

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.

Answer 1

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:

• PostgreSQL: between with datetime