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' : '') : '');
}

You'll probably need a stored procedure or function to do that in MySQL. — liquorvicar, May 26 '12 at 12:51
not good for months and years (second months is 28 days only) — Barbz_YHOOL, Aug 06 '21 at 04:15

Ben · Accepted Answer · 2012-05-26T13:36:21.393

The main problem is as follows:

In order to find the difference between days you need to use datediff()
datediff() returns the difference in days.
In order to convert days to a date, so you can get the number of years etc you need to use from_days()
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.
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.

It is quite weird mysql has nothing easy set up for this issue. I hope they will extend functionality soon :) — Andrius Naruševičius, May 26 '12 at 15:31

score 6 · Answer 2 · answered May 29 '12 at 10:48

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.

Short, concise and right. The correct answer. – jmc Mar 16 '23 at 14:31 — jmc, Mar 16 '23 at 14:31

score 3 · Answer 3 · edited Sep 18 '14 at 22:43

3

I used this:

SELECT TIMESTAMPDIFF(YEAR
       , '2010-08-29'
       , '2014-09-18'
       ) AS Years,
       TIMESTAMPDIFF(MONTH
       , '2010-08-29'
           + INTERVAL TIMESTAMPDIFF(YEAR, '2010-08-29', '2014-09-18') YEAR
       , '2014-09-18'
       ) AS Months,
       TIMESTAMPDIFF(DAY
       , '2010-08-29'
           + INTERVAL TIMESTAMPDIFF(MONTH, '2010-08-29', '2014-09-18') MONTH
       , '2014-09-18'
       ) AS Days

edited Sep 18 '14 at 22:43

Karthik Ganesan

4,142
2
26
42

answered Sep 18 '14 at 22:22

cjmancor

31
1

Using the timestampdiff funtion from cjmancor works for me > TIMESTAMPDIFF(YEAR, '2010-08-29', '2014-09-18') – Trisno Raynaldy Jun 17 '20 at 04:36

score -1 · Answer 4 · answered May 26 '12 at 12:11

-1

SELECT DATE_FORMAT(FROM_DAYS(DATEDIFF('2012-04-30', '2009-05-24')), '%Y-%m-%d') AS `total_days`

answered May 26 '12 at 12:11

Andrius Naruševičius

8,348
7
49
78

2

The function does not seem to be working if the difference is less than a year o.O I was not aware of that. – Andrius Naruševičius May 26 '12 at 14:34

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

4 Answers4

Linked

Related