sql query for days until next birthday?

[eluser]sophistry[/eluser]
I need to display the number of days until someone's next birthday - a birthday countdown.

here is what i came up with. it works (even in leap years), but it's pretty ugly.

anyone have any advice on fixing this?

Code:

$this->db->select("short_name,
DATEDIFF(IF(DAYOFYEAR(CURDATE()) < DAYOFYEAR(CONCAT(YEAR(CURDATE()),'-',MONTH(birthday),'-',DAYOFMONTH(birthday))),
MAKEDATE(YEAR(CURDATE()), DAYOFYEAR(CONCAT(YEAR(CURDATE()),'-',MONTH(birthday),'-',DAYOFMONTH(birthday)))),
MAKEDATE(YEAR(CURDATE())+1, DAYOFYEAR(CONCAT(YEAR(CURDATE()+1),'-',MONTH(birthday),'-',DAYOFMONTH(birthday))))),
CURDATE()) AS days_until_next_birthday", FALSE);

[eluser]Nick Husher[/eluser]

Code:

$this->db->select('short_name, MONTH(birthday) AS mon, DAYOFMONTH(birthday) AS mday');
$result = $this->db->get('birthday_list');

$row = $result->row_array();

$today = time();
$today_parts = getdate($today);

if($today_parts['mday'] <= $row['mday'] && $today)parts['mon'] <= $row['mon']) {
   $birthday_year = $today['year']; // birthday occurs on this calendar year/
} else {
   $birthday_year = $today['year']+1; // birthday occurs next calendar year.
}

$birthday = strtotime($birthday_year."-".$row['mon']."-".$row['mday']);

$days_difference = ($birthday - $today) / (60 * 60 * 24);

Something like that should work. I'm assuming that MONTH and DAYOFMONTH SQL functions return numbers, and you might have to do a little wiggling, but it basically does in PHP what the SQL does, and it's easier to follow.

[eluser]shiggins[/eluser]
Why not use DATEDIFF

[eluser]sophistry[/eluser]
i like the PHP solution because it is more readable. thanks, nick.

@shiggins - could you elaborate? i've been down multiple paths to get to this, so if you have some code to share that would be great!

here's how you can use DATEDIFF to get "age in days", but it quickly becomes more complicated when you want "days until next birthday"

Code:

$this->db->select("short_name, DATEDIFF(CURDATE(), birthday) AS age_in_days");

[eluser]sophistry[/eluser]
i got back to this after a while because there was a bug in the original SQL.

this is much clearer. thanks for the idea to use month and month day comparison instead of day of year.

Code:

// use the second param of select to not escape this string                        
$Ymd = "'".date('Y-m-d')."'";
$this->db->select("DATEDIFF(
                IF( (MONTH(datetime_start) >= MONTH($Ymd)) AND
                     (DAYOFMONTH(datetime_start) >= DAYOFMONTH($Ymd)),
                    CONCAT_WS('-',YEAR($Ymd),MONTH(datetime_start),DAYOFMONTH(datetime_start)),
                    CONCAT_WS('-',YEAR($Ymd)+1,MONTH(datetime_start),DAYOFMONTH(datetime_start))
                ),
                $Ymd
            )                
            AS days_until_event", FALSE);

this works really well for any kind of year-bounded countdown!

[eluser]sophistry[/eluser]
ok, major logic flaw in the previous version... comparing month number and day number is the wrong approach - it doesn't make sense and it gives wrong results.

here is the final version (just sending the SQL select since the other parts were fine):

Code:

$this->db->select("IF (
DATEDIFF( CONCAT_WS('-',YEAR($Ymd),MONTH(datetime_start),DAYOFMONTH(datetime_start)),$Ymd) >= 0,
    DATEDIFF( CONCAT_WS('-',YEAR($Ymd),MONTH(datetime_start),DAYOFMONTH(datetime_start)),$Ymd),
    DATEDIFF( CONCAT_WS('-',YEAR($Ymd)+1,MONTH(datetime_start),DAYOFMONTH(datetime_start)),$Ymd)
)
AS days_until_event", FALSE);