How to convert given number of days to years, months and days in MySQL?

Question

I am trying to get the date and time duration between the Loan taken and Paid date. I used the PHP date and time functions, but it is not always accurate. How can I do this accurately in MySQL?

Let assume two dates, The Loan taken date

2009-05-24

and the Loan return date

2012-04-30

I write a MySQL query

SELECT DATEDIFF('2012-04-30', '2009-05-24') `total_days`;

return 1072 days, which is roughly 2 Years, 11 Months, 12 Days.

Please do not answer with PHP code, I already try it. Here is the code.

The function below uses PHP >= 5.3 functions and convert days to years, months and days.

function date_interval($date1, $date2)
{
    $date1 = new DateTime($date1);
    $date2 = new DateTime($date2);

    $interval = date_diff($date2, $date1);
    return ((($y = $interval->format('%y')) > 0) ? $y . ' Year' . ($y > 1 ? 's' : '') . ', ' : '') . ((($m = $interval->format('%m')) > 0) ? $m . ' Month' . ($m > 1 ? 's' : '') . ', ' : '') . ((($d = $interval->format('%d')) > 0) ? $d . ' Day' . ($d > 1 ? 's' : '') : '');
}

The function below uses PHP >= 5.2 functions and convert days to years, months and days.

function date_interval($date1, $date2)
{
    $diff = abs(strtotime($date2) - strtotime($date1));

    $years = floor($diff / (365 * 60 * 60 * 24));
    $months = floor(($diff - $years * 365 * 60 * 60 * 24) / (30 * 60 * 60 * 24));
    $days = floor(($diff - $years * 365 * 60 * 60 * 24 - $months * 30 * 60 * 60 * 24) / (60 * 60 * 24));

    return (($years > 0) ? $years . ' Year' . ($years > 1 ? 's' : '') . ', ' : '') . (($months > 0) ? $months . ' Month' . ($months > 1 ? 's' : '') . ', ' : '') . (($days > 0) ? $days . ' Day' . ($days > 1 ? 's' : '') : '');
}

Answer 1

The main problem is as follows:

1. In order to find the difference between days you need to use datediff()
2. datediff() returns the difference in days.
3. In order to convert days to a date, so you can get the number of years etc you need to use from_days()

4. from_days() doesn't really work before 1582, to quote from the documentation:

   "Use FROM_DAYS() with caution on old dates. It is not intended for use with values that precede the advent of the Gregorian calendar (1582)"

   The minimum is 1582 as this was when Europe converted from the Julian to the Gregorian calender.

5. 0000-00-00 + 6 days is 0000-01-06, which is earlier than 1582.

This effectively means that MySQL date-functions are useless to you.

You ask for this to be done in MySQL "accurately". As you can't use date functions you're going to have to make up your own. This will not be accurate. How many days are there in a year? It's certainly not always 365. How many days are there in a month?

I would highly recommend doing this in PHP.

However, as you're adamant that you don't want to do so, you're going to have to cheat.

Add the date 1600-01-01 to everything. Then remove 1600 years, 1 month and 1 day from your answer at the end. I only use this date because it's greater than 1582 and it's a nice round number. Anything would work really but the earlier the better so you don't run into problems.

Assuming we've built the following table:

create table dates (a date, b date);
insert into dates
values ( str_to_date('2012-04-30','%Y-%m-%d')
       , str_to_date('2012-04-24','%Y-%m-%d')
        );

insert into dates
values ( str_to_date('2012-04-30','%Y-%m-%d')
       , str_to_date('2009-05-24','%Y-%m-%d')
        );

The following query will get what you want:

select extract(year from from_days(days)) - 1600
     , extract(month from from_days(days)) - 1
     , extract(day from from_days(days)) - 1
  from ( select to_days(a) - to_days(b) + 
                to_days(str_to_date('1600-01-01', '%Y-%m-%d')) as days
           from dates ) as b

Here's a SQL Fiddle to prove it.

Once again, this is really quite hacky and not really recommended.

Answer 2

Use MySQL's TIMESTAMPDIFF() function:

SELECT TIMESTAMPDIFF(YEAR
       , '2009-05-24'
       , '2012-04-30'
       ) AS Years,
       TIMESTAMPDIFF(MONTH
       , '2009-05-24'
           + INTERVAL TIMESTAMPDIFF(YEAR, '2009-05-24', '2012-04-30') YEAR
       , '2012-04-30'
       ) AS Months,
       TIMESTAMPDIFF(DAY
       , '2009-05-24'
           + INTERVAL TIMESTAMPDIFF(YEAR, '2009-05-24', '2012-04-30') YEAR
           + INTERVAL TIMESTAMPDIFF(YEAR, '2009-05-24', '2012-04-30') MONTH
       , '2012-04-30'
       ) AS Days

See it on sqlfiddle.