List:General Discussion« Previous MessageNext Message »
From:Daevid Vincent Date:August 4 2006 7:20am
Subject:Calculating birthdays and distances... Is there a bug?
View as plain text  
I posted this as a comment on the page too, but I’m curious as to why the
top solution is off by a day or so... Is this a bug or a rounding issue or
what? Is there a way to "fix" the top one to work the way I expect/want it
to work? I suspect it's because (as Jack Palance said in 'City Slickers')
"the day ain't over yet" that I get the rounding error.

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html

SET @DOYNOW = DAYOFYEAR(CURDATE());

SELECT (DAYOFYEAR(birthdate) - @DOYNOW) AS birthdays, birthdate, @DOYNOW,
CURDATE() 
FROM users
WHERE birthdate IS NOT NULL;

then if birthdays == 0, it's that persons birthday, otherwise you know if
the birthday is in the future by how many days, or if you missed it and how
many beers you owe them...

(although the missed/negative days seems to be off)

+-----------+------------+---------+------------+
| birthdays | birthdate  | @DOYNOW | CURDATE()  |
+-----------+------------+---------+------------+
|        83 | 1969-10-26 | 216     | 2006-08-04 | 
|         3 | 1981-08-07 | 216     | 2006-08-04 | 
|        -1 | 1972-08-02 | 216     | 2006-08-04 | 
|         0 | 1946-08-04 | 216     | 2006-08-04 | 
|      -151 | 1976-03-05 | 216     | 2006-08-04 | 
+-----------+------------+---------+------------+

Shouldn't that -1 be -2 ?
Am I missing something obvious?

If I do "SELECT DATEDIFF('2006-08-01', CURDATE());" I get -2 as I expect.

So, I guess the real solution is to use this:

SET @YEAR = CONCAT(EXTRACT(YEAR FROM CURDATE()),'-');

SELECT DATEDIFF(CONCAT(@YEAR, DATE_FORMAT(birthdate, '%m-%d')), CURDATE())
AS birthdays, birthdate, CURDATE() 
FROM users
WHERE birthdate IS NOT NULL;

+-----------+------------+------------+
| birthdays | birthdate  | CURDATE()  |
+-----------+------------+------------+
|        83 | 1969-10-26 | 2006-08-04 | 
|         3 | 1981-08-07 | 2006-08-04 | 
|        -2 | 1972-08-02 | 2006-08-04 | 
|         0 | 1946-08-04 | 2006-08-04 | 
|      -152 | 1976-03-05 | 2006-08-04 | 
+-----------+------------+------------+

By the way, if you're using PHP or some other scripting language, you can
get rid of the @YEAR stuff and just do:

DATEDIFF(DATE_FORMAT(birthdate, '".date('Y')."-%m-%d'), CURDATE()) AS
birthdays

ÐÆ5ÏÐ 

Thread
Calculating birthdays and distances... Is there a bug?Daevid Vincent4 Aug
Re: Calculating birthdays and distances... Is there a bug?Chris4 Aug