php - 如何在 MySQL 中将给定的天数转换为年、月和日？

Question

我正在尝试获取贷款日期和支付日期之间的日期和持续时间。我使用了 PHP 日期和时间函数，但它并不总是准确的。我怎样才能在 MySQL 中准确地做到这一点？

假设有两个日期，贷款日期

2009-05-24

和贷款归还日期

2012-04-30

我写了一个 MySQL 查询

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

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

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

下面的函数使用 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' : '') : '');
}

以下函数使用 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' : '') : '');
}

Answer 1

主要问题如下:

1. 为了找出天数之间的差异，您需要使用 datediff()
2. datediff() 返回天数差异。
3. 为了将天数转换为日期，这样你就可以得到你需要使用的年数等from_days()

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

   最小值为 1582，因为这是欧洲从儒略历转换为公历的时间。

5. 0000-00-00 + 6天是0000-01-06，早于1582。

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

您要求在 MySQL 中“准确地”完成此操作。由于您不能使用日期函数，因此您将不得不自己编写。这不会准确。一年有多少天？它当然不总是 365。一个月有多少天？

我强烈建议您在 PHP 中执行此操作。

但是，由于您坚决不想这样做，所以您将不得不作弊。

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

假设我们已经建立了下表:

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

下面的查询会得到你想要的:

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 .

再一次，这真的很老套，不推荐。