List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:July 24 1999 3:18pm
Subject:Re: age
View as plain text  
At 5:07 AM -0500 7/24/99, Michael Farr wrote:
>Hi, what is the easyest way to calculate someones age from their date of
>birth? I have this, but is there something better?
>
>SELECT year(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(dOB))) FROM Person;


You're calculating the difference in days, then converting back to
a date and extracting the year from that.  It might be more efficient
just to divide by 365.25 and chop the fractional part.  For example:

SELECT
BENCHMARK(1000000,
          year(FROM_DAYS(TO_DAYS("1999-7-24")-TO_DAYS("1972-1-1"))));
SELECT
BENCHMARK(1000000,
          FLOOR((TO_DAYS("1999-7-24")-TO_DAYS("1972-1-1"))/365.25));


BENCHMARK() gives you wall clock times; nevertheless, on two unloaded
systems, I get these results:

                    YEAR       FLOOR
LinuxPPC R4        20.70        9.24
300MHz PPC

FreeBSD 3.1        56.84       22.63
180MHz Pentium Pro

The FreeBSD results are for 10,000 iterations only.  With a million
iterations it was taking forever. :-)


I'd suggest running your own tests.  My results may only indicate that
the PPC toasts the Pentium Pro. :-)

I also found that times were quicker for the YEAR method when the dates
were both within the same year.  I don't know what to make of that.

--
Paul DuBois, paul@stripped
Thread
ageMichael Farr24 Jul
  • Re: agetonu24 Jul
    • Re: ageMichael Farr24 Jul
    • Re: agePaul DuBois24 Jul
      • Re: agetonu-mysql25 Jul
  • Re: agePaul DuBois24 Jul
    • BENCHMARK() (was: age)Martin Ramsch26 Jul
      • Re: BENCHMARK() (was: age)Paul DuBois26 Jul
        • Re: BENCHMARK() (was: age)Martin Ramsch26 Jul
Re: age(Timo Maier)26 Jul
  • Re: ageLindsay Davies27 Jul
Re: ageFulko Hew26 Jul