CODEWITHSUNDEEP
mysqltimestampunix-timestamp
James Simpson
James Simpson

Reputation: 13718

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

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.

Upvotes: 16

Views: 31931

Answers (16)

Mustafa
Mustafa

Reputation: 1

Simple way

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

Upvotes: 0

Santosh Sahoo
Santosh Sahoo

Reputation: 11

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

Upvotes: 1

craftsmannadeem
craftsmannadeem

Reputation: 2973

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.

  1. Extract the wish details in excel
  2. 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

Upvotes: -1

Kirara
Kirara

Reputation: 11

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')

Upvotes: 1

Arth
Arth

Reputation: 13110

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 */

Upvotes: 4

EngineHeart
EngineHeart

Reputation: 11

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.

Upvotes: 1

Bhavik Koradiya
Bhavik Koradiya

Reputation: 71

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());

Upvotes: 5

djburner
djburner

Reputation: 21

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.

Upvotes: 2

Thomas K Pedersen
Thomas K Pedersen

Reputation: 11

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.

Upvotes: 1

Dan Soap
Dan Soap

Reputation: 10248

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'

Upvotes: 4

Andrew Moore
Andrew Moore

Reputation: 95484

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'
        )

Upvotes: 13

Jakob Borg
Jakob Borg

Reputation: 24515

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.

Upvotes: 1

user262976
user262976

Reputation: 2074

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);

Upvotes: 0

Pentium10
Pentium10

Reputation: 208042

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'

Upvotes: 1

trinth
trinth

Reputation: 6047

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

Upvotes: 0

grateful.dev
grateful.dev

Reputation: 1437

This should work:

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

Upvotes: 22

Related Questions