Jump to content

Is it your birthday?


fife
Go to solution Solved by Barand,

Recommended Posts

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; 

Link to comment
Share on other sites

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 by dalecosp
Link to comment
Share on other sites

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')
Link to comment
Share on other sites

  • Solution

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 by Barand
Link to comment
Share on other sites

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!

Link to comment
Share on other sites

 

 

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.