Getting all entries whose birthday is today in PostgreSQL

Question

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.

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?

score 16 · Accepted Answer · edited May 06 '16 at 18:26

16

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)

edited May 06 '16 at 18:26

ghickman

5,893
9
42
51

answered Jul 20 '11 at 08:05

Jordan

31,971
6
56
67

1

what about people that are borned last year? – Tudor Constantin Jul 20 '11 at 08:08
3

Apparently i need to go to bed instead of answering questions on SO. I'll edit. – Jordan Jul 20 '11 at 08:09
@jordan - Thanks for the efforts, but none of our clients are 1 day old ;-) – Elitmiar Jul 20 '11 at 08:11
@Jordan - I think I have another problem I get a Syntax error on CURRENT_DATE()), think it might be because of the Old Version of Postgres thats installed on the server , version PostgreSQL 8.1.22 – Elitmiar Jul 20 '11 at 08:18
3

That should be `date_part()` not `datepart()` and `CURRENT_DATE` not `CURRENT_DATE()` – Jul 20 '11 at 08:31
@a_horse_with_no_name: like I said, I should've just gone to bed :) Thanks for the corrections. I have updated my answer with the correct form, for posterity's sake. – Jordan Jul 20 '11 at 14:24
2

Hehe. I still think that my approach is better for people/contracts dated Feb 29th, though. ;-) – Denis de Bernardy Jul 20 '11 at 14:33
Just curious, to enable use of index for this WHERE, do you need to define an index (possibly 2-column) with `date_part('day', column)` ? – androidguy Mar 15 '18 at 06:21
I ended up with this solution too, except that when `CURRENT_DATE` is the Feb 28th on a non-leap year, the query becomes `date_part('month', birthdate) = 2 AND date_part('day', birthdate) IN (28, 29)` to also match people born on leap day. – Qqwy Jul 06 '19 at 21:13

Denis de Bernardy · Answer 2 · 2011-07-20T10:42:22.280

In case it matters, the age function will let you work around the issue of leap years:

where age(cd.birthdate) - (extract(year from age(cd.birthdate)) || ' years')::interval = '0'::interval

It case you want performance, you can actually wrap the above with an arbitrary starting point (e.g. 'epoch'::date) into a function, too, and use an index on it:

create or replace function day_of_birth(date)
  returns interval
as $$
  select age($1, 'epoch'::date)
         - (extract(year from age($1, 'epoch'::date)) || ' years')::interval;
$$ language sql immutable strict;

create index on client_contacts(day_of_birth(birthdate));

...

where day_of_birth(cd.birthdate) = day_of_birth(current_date);

(Note that it's not technically immutable, since dates depend on the timezone. But the immutable part is needed to create the index, and it's safe if you're not changing the time zone all over the place.)

EDIT: I've just tested the above a bit, and the index suggestion actually doesn't work for feb-29th. Feb-29th yields a day_of_birth of 1 mon 28 days which, while correct, needs to be added to Jan-1st in order to yield a valid birthdate for the current year.

create or replace function birthdate(date)
  returns date
as $$
  select (date_trunc('year', now()::date)
         + age($1, 'epoch'::date)
         - (extract(year from age($1, 'epoch'::date)) || ' years')::interval
         )::date;
$$ language sql stable strict;

with dates as (
  select d
  from unnest('{
    2004-02-28,2004-02-29,2004-03-01,
    2005-02-28,2005-03-01
  }'::date[]) d
)
select d,
       day_of_birth(d),
       birthdate(d)
from dates;

     d      | day_of_birth  | birthdate  
------------+---------------+------------
 2004-02-28 | 1 mon 27 days | 2011-02-28
 2004-02-29 | 1 mon 28 days | 2011-03-01
 2004-03-01 | 2 mons        | 2011-03-01
 2005-02-28 | 1 mon 27 days | 2011-02-28
 2005-03-01 | 2 mons        | 2011-03-01
(5 rows)

And thus:

where birthdate(cd.birthdate) = current_date

chandoo · Answer 3 · 2018-04-21T05:20:28.257

The @Jordan answer is correct but, it wont work if your date format is string. If it is string you have type cast it using to_date function. then apply the date_part function.

If date of birth (DOB) is 20/04/1982 then the query is:

SELECT * FROM public."studentData" where date_part('day',TO_DATE("DOB", 'DD/MM/YYYY'))='20' 
AND date_part('month',TO_DATE("DOB", 'DD/MM/YYYY'))='04';

or

EXTRACT(MONTH FROM TO_DATE("DOB", 'DD/MM/YYYY'))='04' AND EXTRACT(DAY FROM TO_DATE("DOB", 'DD/MM/YYYY'))='20'

I add double quotes to table name("studentData") and field name ("DOB") because it was string.

Credit to @Jordan

Lachezar Todorov · Answer 4 · 2011-07-21T11:38:27.487

0

WHERE date_part('month', cd.birthdate) = '07' AND date_part('day', cd.birthdate) = '20'

you can read more about this here

edited Jul 21 '11 at 11:38

answered Jul 20 '11 at 08:18

Lachezar Todorov

903
9
21

Thx, but this error comes up HINT: No function matches the given name and argument types. You may need to add explicit type casts. – Elitmiar Jul 20 '11 at 08:21
2

datepart() is not a Postgres function, it should be date_part() – Jul 20 '11 at 08:28
Yes I missed the underscore. But you shoud use @Jordan solution it's the best. – Lachezar Todorov Jul 21 '11 at 11:38

score 0 · Answer 5 · answered Jul 25 '23 at 09:59

0

WHERE 0 = extract(DAY FROM age(dob)) + extract (MONTH FROM age(dob))

answered Jul 25 '23 at 09:59

Nelson Azevedo

311
3
4

score -1 · Answer 6 · answered Aug 24 '15 at 12:52

-1

The best way IMO is to use to_char(birthday, 'MM-DD') in (?) where you just give some date range mapped to 'MM-DD' in place of ?. Unless you have to support very big date ranges this solution is very simple, clean and bug resistant.

answered Aug 24 '15 at 12:52

user3793800

311
2
3

score -1 · Answer 7 · answered Jun 20 '20 at 08:47

What you are trying to do is, extract the person detail who would be wished using SQL manually, and send the wish separately manually. What if I suggest you a better approach?

Extract the wish details as excel and let wishing app take care of everything.

At minimal it just need two things excel file with wish details (Date, name, email) and a configuration file (application.properties) and that is it, you are good to go.

Further there various options to run the application locally (Command line, foreground, background, docker, windows scheduler, unix cron etc) Cloud.

Application is highly configurable , you can configure various details like:

Workbook loading options
Image options to send with wishes.
SMTP Configurations
Other application level configurations like, when to send wish, belated wish, logging etc.

Disclaimer : I am the owner of the application

score -1 · Answer 8 · answered Jul 20 '11 at 08:12

-1

Try with something like:

WHERE EXTRACT(DOY FROM TIMESTAMP cd.birthdate) = EXTRACT(DOY FROM TIMESTAMP CURRENT_TIMESTAMP)

answered Jul 20 '11 at 08:12

Tudor Constantin

26,330
7
49
72

I thought about that too - Jordan's answer is the good one then :) – Tudor Constantin Jul 20 '11 at 08:17
No, it isn't... He's extracting the year instead of the day, and that still won't solve the leap year. I'd use age(), personally (see my answer). – Denis de Bernardy Jul 20 '11 at 08:23

Getting all entries whose birthday is today in PostgreSQL

8 Answers8

Linked