Is it your birthday?

[fife]

Hi Guys.

 

Im just trying to find a list of people with birthdays coming up in the next 14 days from a table in my db.  I'm storing birthday as a unix timestamp.  Currently I have one birthday coming up on the 10/12/1984   or unix time would be  471484800.  My query is returning 0 as the number of birthdays when it should say 1.

 

mysql_select_db($database_dbconnect, $dbconnect);$query_Birth = "SELECT StaffDOB FROM Staff WHERE StaffDOB  BETWEEN UNIX_TIMESTAMP(CURDATE()) AND UNIX_TIMESTAMP(ADDDATE(CURDATE(), INTERVAL 14 DAY))";$Birth = mysql_query($query_Birth, $dbconnect);$totalRows_Birth = mysql_num_rows($Birth);  echo $totalRows_Birth; 

[dalecosp]

The problem is that you've recorded the birth-YEAR in the UNIX timestamp.  1984 was 29 years ago, and is (fairly obviously) not between CURDATE and CURDATE+14.

 

I'd go out on a limb and say that maybe a Unix TS (as much as I like to use them) wasn't the best choice for a birthdate here.

There might be some magic in SQL that's beyond me that could help, but I'd recommend thinking up an additional table and scripting something in PHP to get some birthday data that you can use more easily.

Edited December 6, 2013 by dalecosp

[mac_gyver]

to find birthdays between a range, you would need to test the month and day only.

 

assuming your StaffDOB is actually a mysql DATE data type, so that you can use mysql's date functions on it, your WHERE term would be -

WHERE DATE_FORMAT(StaffDOB,'%m%d') BETWEEN DATE_FORMAT(CURDATE(),'%m%d') AND DATE_FORMAT(CURDATE() + INTERVAL 14 DAY,'%m%d')

[dalecosp]

So, mac_gyver, could he use FROM_UNIXTIME() in that query to make it happen without changing his DB schema?

 

 

WHERE DATE_FORMAT(FROM_UNIXTIME(StaffDOB),'%m%d') BETWEEN DATE_FORMAT(CURDATE(),'%m%d') AND DATE_FORMAT(CURDATE() + INTERVAL 14 DAY,'%m%d');

[dalecosp]

And the answer: yes.  I'd do it that way, fife.

[Barand]

... unless CURDATE() is in the last two weeks of December

[dalecosp]

... unless CURDATE() is in the last two weeks of December

Hmm, yes.  So, what, then ... a DATEDIFF() call, maybe?

[.josh]

oh. I came in here thinking I was gonna get a present or something.

[Barand]

If their birthday has already passed by this year then the next birthday is next year otherwise it is this year, so

$sql = "SELECT  FROM_UNIXTIME(staffDOB) as dob
 FROM staff
WHERE CASE
   WHEN DATE_FORMAT(FROM_UNIXTIME(staffDOB),'%m%d') < DATE_FORMAT(CURDATE(),'%m%d')
        THEN CONCAT(YEAR(CURDATE())+1, DATE_FORMAT(FROM_UNIXTIME(staffDOB),'-%m-%d'))
    ELSE CONCAT(YEAR(CURDATE()), DATE_FORMAT(FROM_UNIXTIME(staffDOB),'-%m-%d'))
    END 
BETWEEN CURDATE() AND CURDATE()+INTERVAL 14 DAY";

Fife, please use DATE type fields and not unix timestamps - it saves all that conversion when processing dates

Edited December 6, 2013 by Barand

[fife]

Hi Guys.  Sorry for not catching up with you all  Ive been away.  Thank you all very much for your help.  I've always used and worked with unix timestamp as I was taught that this was the best way to store dates.  Barand as usual your solution is perfect and I will mark it so.

 

So for a follow up am I right in using unix timestamps? I understand its not the best for birthdays now but  I'm building a massive employee management and payment system where I will be working with dates and comparing dates a lot.  

 

For example an employee will have holidays.  Currently I'm storing the start date of the holiday and the end date as unix times.  Is this a bad Idea?  I was going to do the same with the rota system.  If so what would be a better solution? I'd rather change it now while the site is still in its infancy before I run into major issues later.  Again thanks Freakers!

[Barand]

 

 

So for a follow up am I right in using unix timestamps?

 

I thought I had answered that one.

 

MySQL gave us DATE, DATETIME and TIMESTAMP type specifically for storing dates and times and also dozens of datetime functions to make life easier when dealing with them. If you use other types then you will always have to do a conversion before you can use these functions.

 

In addition, these date types have the advantage of being "human-readable". If you ever have to browse your db tables when you have problems (and you will) then a value like "1386591094" is meaningless to mere mortals whereas "2013-12-09 12:11:34" is instantly recognizable.

[fife]

Wicked thanks again Barand you are a world of knowledge as usual.  I will be updating my site.