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
