How to get the last occurrence of a date?-CodePudding

I have a table like the following

name	birthdate
John	1980-10-25
Mike	1975-09-17
Jill	1987-01-13
Anne	1999-02-01

Today's date is 2022-02-22.

How would I write a sql query that returns the name and last time their birthday occurred? As in the same day and month from 2022 if their birthday has already happened this year or from 2021 if their birthday hasn't happened this year

Sample response would be

name	last_birthdate
John	2022-10-25
Mike	2022-09-17
Jill	2021-01-13
Anne	2021-02-01

CodePudding user response：

Use date arithmetic:

SELECT name,
       birthdate   EXTRACT (year FROM
                            age(current_timestamp, birthdate)
                           ) AS last_birthday
FROM tab;

CodePudding user response：

You can use a conditional:

select name, case when date(extract(year from now())||regexp_replace(birthdate::text, '^\d ', '')) < date(now()) then extract(year from now())-1||regexp_replace(birthdate::text, '^\d ', '') else extract(year from now())||regexp_replace(birthdate::text, '^\d ', '') end from people

CodePudding user response：

select 
    name, 
    case 
        when
            birthdate   concat(extract(YEAR from now()) - extract(YEAR FROM birthdate), ' years')::interval > now()
        then 
            birthdate   concat(extract(YEAR from now()) - extract(YEAR FROM birthdate) - 1, ' years')::interval
        else 
            birthdate   concat(extract(YEAR from now()) - extract(YEAR FROM birthdate), ' years')::interval
    end as last_birthdate
from tab