On Sat, 30 Mar 2002, Paul DuBois wrote:
> At 17:15 -0500 3/30/02, Michael Stassen wrote: > >On Sat, 30 Mar 2002, Paul DuBois wrote: > > > >> At 21:25 +0100 3/30/02, Roger Baklund wrote: > >> >* Son Nguyen > >> >> ==> I tried this query, but... it's not working out: > >> >> SELECT YEAR(now()-birthday) from users where loginName="test"; > >> > > >> >You are very close: > >> > > >> >SELECT YEAR(now())-YEAR(birthday) as age from users where loginName="test"; > >> > >> That won't adjust for the relative difference within the calendar > >> year of the two dates. This works better: > >> > >> mysql> SELECT > >> -> YEAR(CURDATE()) - YEAR(birthday) > >> -> - IF(RIGHT(CURDATE(),5) < RIGHT(birthday,5),1,0) > >> -> AS 'age in years' > >> -> FROM users WHERRE loginName="test"; > > > >Or > > > > SELECT > > YEAR(FROM_DAYS(TO_DAYS(CURDATE()) - TO_DAYS(birthday))) > > AS 'age in years' > > FROM users WHERE loginName='test'; > > > >Michael > > mysql> SET @birth = CURDATE() - INTERVAL 1 YEAR; > mysql> SELECT > -> CURDATE(), @birth, > -> YEAR(FROM_DAYS(TO_DAYS(CURDATE()) - TO_DAYS(@birth))) > -> AS 'age in years'; > +------------+------------+--------------+ > | CURDATE() | @birth | age in years | > +------------+------------+--------------+ > | 2002-03-30 | 2001-03-30 | 0 | > +------------+------------+--------------+ Yes, it seems from_days(N) expects N to be days since 0000-00-00, rather than 0000-01-01, which is reasonable. Thus, 0001-01-01 is from_days(366), so I was off by one day. I should have said YEAR(FROM_DAYS(1 + TO_DAYS(CURDATE()) - TO_DAYS(birthday))). On the other hand, now that I think about it, there may be certain combinations of CURDATE and birthday which will still be off by one (for a day) due to leap year. So your way is better (and it's in the manual! <http://www.mysql.com/doc/D/a/Date_calculations.html>). > mysql> SELECT > -> CURDATE(), @birth, > -> YEAR(CURDATE()) - YEAR(@birth) > -> - IF(RIGHT(CURDATE(),5) < RIGHT(@birth,5),1,0) > -> AS 'age in years'; > +------------+------------+--------------+ > | CURDATE() | @birth | age in years | > +------------+------------+--------------+ > | 2002-03-30 | 2001-03-30 | 1 | > +------------+------------+--------------+ While looking into this, however, I ran across some strange behavior for from_days and to_days which I didn't expect. ==== from_days(N) appears to work only for N>=366. mysql> select from_days(1), from_days(365), from_days(366); +--------------+----------------+----------------+ | from_days(1) | from_days(365) | from_days(366) | +--------------+----------------+----------------+ | 0000-00-00 | 0000-00-00 | 0001-01-01 | +--------------+----------------+----------------+ I would have expected 0000-01-01, 0000-12-31, 0001-01-01, respectively. Perhaps there's a good reason for this. If not, I'd call it a bug. ==== to_days(D) appears to work only for D >= 0200-01-01 mysql> select D, to_days(D), from_days(to_days(D)) from day_test; +------------+------------+-----------------------+ | D | to_days(D) | from_days(to_days(D)) | +------------+------------+-----------------------+ | 0000-00-00 | NULL | NULL | | 0000-01-01 | 730485 | 2000-01-01 | | 0000-12-31 | 730850 | 2000-12-31 | | 0001-01-01 | 730851 | 2001-01-01 | | 0069-01-01 | 755688 | 2069-01-01 | | 0070-01-01 | 719528 | 1970-01-01 | | 0099-01-01 | 730120 | 1999-01-01 | | 0100-01-01 | 730485 | 2000-01-01 | | 0169-01-01 | 755688 | 2069-01-01 | | 0170-01-01 | 756053 | 2070-01-01 | | 0199-01-01 | 766645 | 2099-01-01 | | 0200-01-01 | 73049 | 0200-01-01 | +------------+------------+-----------------------+ I imagine this is Y2K related, as it seems to be treating my 4-digit years as if they were 2-digit for 0000 to 0099, or 3-digit for 0100 to 0199. But I really did specify 4 digits! Perhaps there's a good reason to do this, but again, if there isn't, I'd call it a bug. Of course, I don't expect MySQL to be too worried about these. They did tell me, after all, that to_days and from_days aren't intended for dates before 1582, due to the calendar change, and the date type itself isn't supported before year 1000. But, I think there's a difference between saying the output will be inaccurate for early dates because the calendar changed and saying your output will be wrong even if you pretend the current calendar was always in effect. Regardless of calendar changes, I would have expected FROM_DAYS(TO_DAYS(D)) = D and TO_DAYS(FROM_DAYS(N)) = N. I would have been wrong for D < 0200-01-01 and N < 73049. At the very least, it would be nice if the ranges for which these functions are mathematically valid were included in the docs. Now that I've made a mountain out of a molehill, I think the real problem is that while we have methods to add/subtract an interval to/from a date to get a new date, we don't have a method to subtract two dates (or datetimes) to get an interval (in days or months or years or...). It seems to come up a lot, but I don't see it on the TODO lists. Michael --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
