在PostgreSQL中获取所有生日为今天的条目 [英] Getting all entries whose birthday is today in PostgreSQL

问题描述

我有以下查询，我需要实现一个Mailer，该Mailer必须发送给今天是Birthday的所有客户。这每天都会发生。现在，我只需要使用Postgres SQL查询来选择Birthday客户，而不是在PHP中对其进行过滤。

I have the following query and 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.

数据库中存储的日期格式为YYYY- MM-DD例如1984-03-13

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

我的查询如下

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?

推荐答案

您可以将where子句设置为：

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)

这篇关于在PostgreSQL中获取所有生日为今天的条目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！