Roland Roland - 7 months ago 26
PHP Question

Getting all entries who's Birthday is today in PostgreSQL

I have the following query, I need to implement a Mailer that needs to be send out to all clients who's Birthday is today, this happens on a daily manner, now what I need to achieve is only to select the Birthday clients using a Postgres SQL query instead of filtering them in PHP.

The date format stored in the database is YYYY-MM-DD eg. 1984-03-13

What I have is the following query

SELECT cd.firstname,
cd.surname,
SUBSTRING(cd.birthdate,6),
cd.email
FROM client_contacts AS cd
JOIN clients AS c ON c.id = cd.client_id
WHERE SUBSTRING(birthdate,6) = '07-20';


Are there better ways to do this query than the one I did above?

Answer

You could set your where clause to:

WHERE
    DATE_PART('day', birthdate) = date_part('day', CURRENT_DATE)
AND
    DATE_PART('month', birthdate) = date_part('month', CURRENT_DATE)