如何将给定天数转换为MySQL中的年，月和日？

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?

我正在尝试获取贷款和付费日期之间的日期和时间。我使用了PHP日期和时间函数,但它并不总是准确的。我怎样才能在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

我写了一个MySQL查询

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

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

返回1072天,大约2年,11个月,12天。

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

请不要用PHP代码回答,我已经尝试过了。这是代码。

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

下面的函数使用PHP> = 5.3函数并将天数转换为年,月和日。

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.

下面的函数使用PHP> = 5.2函数并将天数转换为年,月和日。

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

4 个解决方案

#1

The main problem is as follows:

主要问题如下:

In order to find the difference between days you need to use datediff()

为了找到天数之间的差异你需要使用datediff()

datediff() returns the difference in days.

datediff()返回天数差异。

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()

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

from_days()在1582年之前没有真正起作用,引用文档:

"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)"

“在旧日期时谨慎使用FROM_DAYS()。它不适用于格里高利历(1582年)出现之前的值。”

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

最小的是1582,因为这是欧洲从朱利安转换为格里高利日历的时候。
0000-00-00 + 6 days is 0000-01-06, which is earlier than 1582.

0000-00-00 + 6天是0000-01-06,早于1582。

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

这实际上意味着MySQL日期函数对您来说毫无用处。

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?

您要求“准确地”在MySQL中完成此操作。由于你不能使用日期功能,你将不得不自己组成。这不准确。一年有多少天?它肯定不总是365.一个月有多少天?

I would highly recommend doing this in PHP.

我强烈建议在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.

将日期1600-01-01添加到所有内容。然后从最后的答案中删除1600年,1个月和1天。我只使用这个日期,因为它大于1582,这是一个很好的整数。任何事情都会有效,但越早越好,所以你不会遇到问题。

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.

这是一个证明它的SQL小提琴。

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

再次,这真的很hacky并没有真正推荐。

#2

Use MySQL's TIMESTAMPDIFF() function:

使用MySQL的TIMESTAMPDIFF()函数:

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.

在sqlfiddle上看到它。

#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

#4

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

#1