Calculate date of birth from age at specific date [MySQL or Perl]

Apologies if this is a really simple question but I am interested in trying to reach an accurate answer and not just a "rounded" up answer.

My problem is: I know somebody is 27.12 on the 18th of March 2008 (random example). How can I calculate, to the nearest approximation, 开发者_JAVA技巧his date of birth. Age is always provided as a real number to two decimal points.

The solutions through simple fractional calculation are 1981-02-03 and the day before, due to rounding. As eumiro said, the resolution of 1/100 year is not precise enough, so it might still be off a day or two with the real date.

use DateTime qw();
use POSIX qw(modf);

my $date = DateTime->new(year => 2008, month => 3, day => 18);    # 2008-03-18
my $age = 27.12;                                                  # 27.12
my ($days, $years) = modf $age;                                   # (0.12, 27)
$days *= 365.25;                                                  # 43.83
# approx. number of days in a year, is accurate enough for this purpose

$date->clone->subtract(years => $years, days => $days);        # 1981-02-03
$date->clone->subtract(years => $years, days => 1 + $days);    # 1981-02-02

eumiro's answer does the trick; the following, using the Time::Piece module (bundled with Perl since 5.10) is perhaps more maintainable.

use strict;
use warnings;
use 5.010;

use Time::Piece;
use Time::Seconds;

my ($date, $age) = ('2008-03-18', 27.12);

my $birthday = Time::Piece->strptime($date, '%Y-%m-%d') - $age*ONE_YEAR;
say $birthday->ymd();

This will get you within a few days of the actual birthday, due to the lack of accuracy (1/100 year) in the age.

use strict;
use Time::Local;

my $now = timelocal(0, 0, 12, 18, 3-1, 2008);
my $birthday = $now - 27.12 * 365.25 * 86400;
print scalar localtime $birthday;

returns Mon Feb 2 22:04:48 1981.

Your precision is 0.01 year, which is roughly 3 days, so you even cannot cover all birthdays.

My method does not cover leap years very well, but you cannot really calculate exactly with them. Imagine the 01-March-2008. What date was "1 year and 1 day" before this date? 28-February-2007 or the not existing 29-February-2007?

A method that permits greater accuracy simply takes advantage of existing MySQL Date/Time functions. If working inside the MySQL, you can calculate the age with great precision by converting each of two dates to seconds in the TO_SECONDS() conversion and then manipulating the results to the desired precision. In these cases, the dates are in 'yyyy-mm-dd hh:mm:ss' formats and a year is assumed to have mean length of 365.242 days.

ROUND((TO_SECONDS(AnyDateTime) - TO_SECONDS(DateOfBirth))/(365.242*60*60*24),3) as age, e.g.:

    ROUND((TOSECONDS('2013-01-01 00:00:00') - TO_SECONDS('1942-10-16')/(365.242*60*60*24),3) as AGE -->  70.214

Alternatively you can use the DATEDIFF() conversion which provides the answer in days:

    ROUND(DATEDIFF('2013-01-01 00:00:00','1942-10-16')/365.242,3) AS age -->  70.214