Use MySQL to determine whether today is a user's birthday

Question

I have all users' birthdays stored as a UNIXtimestamp and am wanting to send out e-mails each day to users that have a birthday that day.

I need to make a MySQL query that will get all of the rows that contain a birthday on today's date.

It seems like this should be fairly simple, but maybe I am just overcomplicating it.

As a side note: `birthdays stored as a unix timestamp`: You should think about changing this to using DATETIME. Timestamp can only store dates from 1970-01-01 to something around 2038. Once the first user enters a birthdate prior 1970, you will get errors. Check with the documentation on mysql! — Dan Soap, Feb 07 '10 at 22:20
Can you assume all your users are in roughly the same timezone? — Mike Samuel, Jan 25 '12 at 19:03

score 22 · Accepted Answer · answered Feb 07 '10 at 21:24

22

This should work:

   SELECT * 
      FROM USERS
      WHERE 
         DATE_FORMAT(FROM_UNIXTIME(birthDate),'%m-%d') = DATE_FORMAT(NOW(),'%m-%d')

answered Feb 07 '10 at 21:24

grateful.dev

1,437
10
21

6

This doesn't take into account february 29, when there aren't leap years. The user has to send out emails on 28th february. You have to include 29th feb (if there isn't that day) into 28th feb. – Pentium10 Feb 07 '10 at 21:31
15

February 29th birthday kids are already used to celebrate their birthday only once every 4 years. They get the no-birthday-spam privilege. Fair deal, isn't it? – grateful.dev Feb 07 '10 at 21:37
Let's take this serious, whould you miss a Wireless Carrier bonus for your birthday 3 years in row? – Pentium10 Feb 07 '10 at 21:40
I can use PHP to alter the mysql query if they have a feb 20 birthday. – James Simpson Feb 07 '10 at 21:43
bad approach, see my answer below – Pentium10 Feb 07 '10 at 21:46

score 12 · Answer 2 · answered Feb 07 '10 at 22:08

Here is an answer that property takes into account leap-years and will always give you the users whose birthday is on the 29th of February at the same time as those on the 1st of March.

SELECT * 
  FROM USERS
  WHERE 
     DATE_FORMAT(FROM_UNIXTIME(birthDate),'%m-%d') = DATE_FORMAT(NOW(),'%m-%d')
     OR (
            (
                DATE_FORMAT(NOW(),'%Y') % 4 <> 0
                OR (
                        DATE_FORMAT(NOW(),'%Y') % 100 = 0
                        AND DATE_FORMAT(NOW(),'%Y') % 400 <> 0
                    )
            )
            AND DATE_FORMAT(NOW(),'%m-%d') = '03-01'
            AND DATE_FORMAT(FROM_UNIXTIME(birthDate),'%m-%d') = '02-29'
        )

It works fine if you remove `FROM_UNIXTIME `. – iphonic Jul 08 '22 at 08:03 — iphonic, Jul 08 '22 at 08:03

score 4 · Answer 3 · answered Feb 07 '10 at 23:12

Since this gets more and more to be a code-golf question, here's my approach on solving this including taking care of the leap years:

select * 
from user
where (date_format(from_unixtime(birthday),"%m-%d") = date_format(now(),"%m-%d"))
   or (date_format(from_unixtime(birthday),"%m-%d") = '02-29'
       and date_format('%m') = '02' 
       and last_day(now()) = date(now())
      );

Explanation: The first where clause checks if somebody's birthday is today. The second makes sure to only select those whose birthday is on Feb 29th only if the current day equals the last day of February.

Examples:

SELECT last_day('2009-02-01'); -- gives '2009-02-28'
SELECT last_day('2000-02-01'); -- gives '2009-02-29'
SELECT last_day('2100-02-01'); -- gives '2100-02-28'

This is my favourite as it is readable and doesn't use too many functions. I would however be tempted to check that the current date is the last day of feb (not row dependent) before checking that the user was born on the feb 29th (row dependent) on the off chance that the engine can use this and hasn't figured it out already. — Arth, Oct 01 '15 at 16:02

score 4 · Answer 4 · edited Jul 05 '16 at 15:35

4

You can use the query below if date of birth stored in a table.

Today Birthday :

select * from TABLENAME
 where DAY(FIELDNAME) = DAY(CURDATE())
   and MONTH(FIELDNAME) = MONTH(CURDATE());

Yesterday Birthday:

select * from TABLENAME
 where DAY(FIELDNAME) = DAY(DATE_ADD(CURDATE(), INTERVAL -1 DAY))
   and MONTH(FIELDNAME) = MONTH(CURDATE());

Tomorrow Birthday:

select * from TABLENAME
 where DAY(FIELDNAME) = DAY(DATE_ADD(CURDATE(), INTERVAL 1 DAY))
   and MONTH(FIELDNAME) = MONTH(CURDATE());

edited Jul 05 '16 at 15:35

DreadPirateShawn

8,164
4
49
71

answered Jul 08 '15 at 11:33

Bhavik Koradiya

61
2

Note that the yesterday/tomorrow checks will fail for dates on the first or last day of a month -- should avoid resolving to the day/month until after comparing raw date distances. – DreadPirateShawn Jul 05 '16 at 15:39

Arth · Answer 5 · 2017-10-26T11:48:09.690

This should cover the leap year cases, and uses the internal date mechanics.

Basically it works by adding the years between the two dates to the date of birth and checks for equality with the current date:

WHERE dob + INTERVAL (YEAR(CURDATE()) - YEAR(dob)) YEAR = CURDATE();

Testing:

SELECT '2012-02-29' 
       + INTERVAL (YEAR('2015-02-28') - YEAR('2012-02-29')) YEAR 
       = '2015-02-28'; /* 1, is birthday */

SELECT '2012-02-28' 
       + INTERVAL (YEAR('2015-02-28') - YEAR('2012-02-28')) YEAR  
       = '2015-02-28'; /* 1, is birthday */

SELECT '2012-02-28'
       + INTERVAL (YEAR('2016-02-29') - YEAR('2012-02-28')) YEAR 
       = '2016-02-29'; /* 0, is NOT birthday  */

SELECT '2012-02-29'
       + INTERVAL (YEAR('2016-02-29') - YEAR('2012-02-29')) YEAR 
       = '2016-02-29'; /* 1, is birthday */

score 2 · Answer 6 · edited Nov 08 '12 at 09:31

I come across with this problem, and I just used this simple code using the NOW();

$myquery = "SELECT username FROM $tblusers WHERE NOW() = bd";

The results are today's birthdays so after that I working in sending emails to my users on their birthday.

I store my users bithdays using just the DATE so I always have yy:mm:dd, so this works like a charm, at least to me, using this approach.

score 1 · Answer 7 · edited Jun 20 '20 at 09:12

1

The answer below doesn't actually work. It doesn't take into account the fact that a year is 365.24 (leap days now and then) days long, so the actual comparison against the users birthdate is complicated to say the least. I'm leaving it for historical reasons.

The other answers should work but if you want a slight optimization, say if there are many many rows, you are probably better off expressing the query directly in timestamp seconds. You can use the relations (slightly involved because of taking timezone into account):
today_starts = UNIX_TIMESTAMP(NOW()) - TIMESTAMPDIFF(SECOND, DATE(NOW()), NOW())
today_ends = today_starts + 86400
and then select records where the timestamp is between those values.

edited Jun 20 '20 at 09:12

Community

1
1

answered Feb 07 '10 at 21:27

Jakob Borg

23,685
6
47
47

2

This doesn't take into account february 29, when there aren't leap years. The user has to send out emails on 28th february. You have to include 29th feb (if there isn't that day) into 28th feb. – Pentium10 Feb 07 '10 at 21:32
1

@Pentium Actually, the more I think about it, the buggier this is. I'll edit and leave it as a warning against this kind of optimization. :) – Jakob Borg Feb 07 '10 at 21:52
this reminds me of a quote I once heard: "It could be that the purpose of your life is only to serve as a warning to other". Kudos to you for standing to your answer! (btw: http://www.despair.com/mis24x30prin.html ) – Dan Soap Feb 07 '10 at 22:04

score 1 · Answer 8 · answered Feb 07 '10 at 21:46

1

I took Saggi Malachi's answer and extended to include a birthday on 29th February into 28th February date, if in that year there is no such day.

SELECT * 
      FROM USERS
      WHERE 
         DATE_FORMAT(FROM_UNIXTIME(birthDate),'%m-%d') = DATE_FORMAT(NOW(),'%m-%d')
UNION
SELECT * 
      FROM USERS
      WHERE 
         DATE_FORMAT(NOW(),'%Y')%4 != 0 AND DATE_FORMAT(NOW(),'%m-%d')='02-28' and DATE_FORMAT(FROM_UNIXTIME(birthDate),'%m-%d') = '02-29'

answered Feb 07 '10 at 21:46

Pentium10

204,586
122
423
502

2

This doesn't properly check for leap years. A leap year occurs every 4 years unless the year is divisible by 100 and not by 400. `isLeap = (year % 4) == 0 && !((year % 100) == 0 && (year % 400) != 0) – Andrew Moore Feb 07 '10 at 22:00
2

technically that is not how you detect a leap year (1900, 2100) are not leap years. – user262976 Feb 07 '10 at 22:00
1

Actually, if you need to be serious about this, you have to take into account, that every 100 years, there's no leap year, but every 400 there is ... You wouldn't wanna send the leap-day-birthday kids two mails on Feb 28th and Feb 29th 2100 :-) – Dan Soap Feb 07 '10 at 22:00
1

I am very much against adding any database overhead for such cases, this should be handled in your code and only run once a year. Databases are a very expensive resource, respect it. Also, why using UNION and not an OR? – grateful.dev Feb 07 '10 at 22:05
"Databases are a very expensive resource"? On the contrary; perform your data manipulation as early as you can to save resources. – Lightness Races in Orbit Jul 12 '11 at 10:38

score 1 · Answer 9 · edited Jul 05 '16 at 16:10

Enjoy :)

select p.birthday, 
CASE YEAR(p.birthday)%4 + MONTH(p.birthday)-2 + dayofmonth(p.birthday)-29 WHEN 0 THEN 1 ELSE 0 END as isBirthday29Feb,
CASE YEAR(now())%4  WHEN 0 THEN 1 ELSE 0 END as isThisYearLeap,
IF(YEAR(p.birthday)%4 + MONTH(p.birthday)-2 + dayofmonth(p.birthday)-29=0 AND YEAR(now())%4 != 0,
            DATE_ADD(DATE_ADD(p.birthday, INTERVAL 1  DAY), INTERVAL YEAR(NOW())-YEAR(p.birthday)  YEAR) ,
            DATE_ADD(p.birthday, INTERVAL YEAR(NOW())-YEAR(p.birthday)  YEAR)  
)as thisYearBirthDay
from person p;

This gives you a person's birthday calculated according the current year. Then you can use it for other calculations! The columns isBirthday28Feb and isThisYearLeap are given just to illustrate the solution.

score 1 · Answer 10 · answered Jun 03 '10 at 09:02

1

Here's my contribution

SELECT
  DAYOFYEAR(CURRENT_DATE)-(dayofyear(date_format(CURRENT_DATE,'%Y-03-01'))-60)=
  DAYOFYEAR(the_birthday)-(dayofyear(date_format(the_birthday,'%Y-03-01'))-60)
FROM
   the_table

The bits '(dayofyear(date_format(current_date,'%Y-03-01'))-60)' returns 1 on leap years since march 1st will be dayofyear number 61, and 0 on normal years.

From here it's just a matter of substracting that extra day to the "is-it-my-birthday"-calculation.

answered Jun 03 '10 at 09:02

Thomas K Pedersen

11
1

On the 1st of Jan for a `current_date` of a leap year and the 1st of Jan for a `the_birthday` of a non leap year you get `0 = 1`.. – Arth Oct 01 '15 at 15:51

score 1 · Answer 11 · answered Jun 17 '20 at 07:12

I took Saggi's answer and thought about modifying it so that it would show birthdays for next 7 days and noticed it also neatly solves the leap year problem :)

SELECT * 
   FROM USERS
   WHERE 
      DATE_FORMAT(FROM_UNIXTIME(birthDate),'%m-%d') >= DATE_FORMAT(NOW(),'%m-%d') AND
      DATE_FORMAT(FROM_UNIXTIME(birthDate),'%m-%d') <  DATE_FORMAT(NOW()+INTERVAL 1 DAY,'%m-%d')

Note it doesn't include the upper bound. For leap years nothing falls between '02-28' and '02-29', but for non-leap years the '02-29' falls between '02-28' and '03-01'.

If you'd want the next 7 days, use:

SELECT * 
   FROM USERS
   WHERE 
      DATE_FORMAT(FROM_UNIXTIME(birthDate),'%m-%d') BETWEEN
      DATE_FORMAT(NOW(),'%m-%d') AND DATE_FORMAT(NOW()+INTERVAL 7 DAY,'%m-%d')

score 1 · Answer 12 · answered Apr 07 '22 at 11:07

1

SELECT * FROM user WHERE DATE_FORMAT((birthDate),'%m-%d') = DATE_FORMAT(NOW(),'%m-%d')

answered Apr 07 '22 at 11:07

Santosh Sahoo

11
1

While this code may solve the question, [including an explanation](//meta.stackexchange.com/q/114762) of how and why this solves the problem would really help to improve the quality of your post, and probably result in more up-votes. Remember that you are answering the question for readers in the future, not just the person asking now. Please [edit] your answer to add explanations and give an indication of what limitations and assumptions apply. – Yunnosch Jul 23 '22 at 11:08

user262976 · Answer 13 · 2010-02-07T21:51:18.723

0

set @now=now();
select * from user where (month(birthday) = month(@now) and day(birthday) = day(@now)) or
  (month(birthday) = 2 and day(birthday) = 29 and month(@now) = 2 and day(@now) = 28 and
  month(date_add(@now, interval 1 day)) = 3);

edited Feb 07 '10 at 21:51

answered Feb 07 '10 at 21:22

user262976

1,994
12
7

1

This doesn't take into account february 29, when there aren't leap years. The user has to send out emails on 28th february. You have to include 29th feb (if there isn't that day) into 28th feb. – Pentium10 Feb 07 '10 at 21:31

score 0 · Answer 14 · answered Feb 07 '10 at 21:25

Couldn't you just select all rows that matched the current day's date? You could also use the FROM_UNIXTIME() function to convert from unix timestamp to Date:

mysql> SELECT FROM_UNIXTIME(1196440219); -> '2007-11-30 10:30:19'

This is documented from http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_from-unixtime

score 0 · Answer 15 · edited Sep 21 '22 at 10:56

0

Simple way

SELECT * FROM users WHERE MONTH(brith_day_table) = MONTH(NOW()) AND DAY(birth_day_table) = DAY(NOW())

edited Sep 21 '22 at 10:56

Abdulla Nilam

36,589
17
64
85

answered Sep 15 '22 at 20:25

Mustafa

1
2

score -1 · Answer 16 · answered Jun 20 '20 at 08:50

What you are doing currently is determine if today is users birthday using sql and if yes send the wish separately, there is better approach to handling this.

Extract the wish details in excel
let Wishing Application take care of the rest

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

This doesn't answer the question at all. – henritroyat Sep 24 '20 at 17:20 — henritroyat, Sep 24 '20 at 17:20

Use MySQL to determine whether today is a user's birthday

16 Answers16

Linked

Related