Как преобразовать заданное количество дней в годы, месяцы и дни в MySQL?

Я пытаюсь получить дату и продолжительность времени между датой взятия кредита и датой оплаты. Я использовал функции даты и времени PHP, но они не всегда точны. Как я могу сделать это точно в MySQL?

Предположим, что две даты, Дата взятия кредита

2009-05-24

И дата возврата кредита

2012-04-30

Я пишу запрос MySQL

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

Верните 1072 дня, что составляет примерно 2 года, 11 месяцев, 12 дней.

Пожалуйста, не отвечайте PHP-кодом, я уже пробовал. Вот код.

Функция ниже использует функции 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' : '') : '');
}
Author: Madan Sapkota, 2012-05-26
Source

4 answers

Основная проблема заключается в следующем:

  1. Для того, чтобы найти разницу между днями, вам нужно использовать datediff()
  2. datediff() возвращает разницу в днях.
  3. Для преобразования дней в дату, чтобы вы могли получить количество лет и т. Д., Которое вам нужно использовать from_days()

  4. from_days() на самом деле не работает до 1582 года, цитирую из документации:

    "Используйте FROM_DAYS() с осторожностью в старые даты. Он не предназначен для использования со значениями, которые предшествуют появлению григорианского календаря (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

Вот скрипка SQL, чтобы доказать это.

Еще раз, это действительно довольно банально и не очень рекомендуется.

 6
Author: Ben, 2012-05-26 13:36:21

Используйте 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

Смотрите его на sqlfiddle.

 4
Author: eggyal, 2012-05-29 10:48:57

Я использовал это: 

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
 2
Author: cjmancor, 2014-09-18 22:43:45 
SELECT DATE_FORMAT(FROM_DAYS(DATEDIFF('2012-04-30', '2009-05-24')), '%Y-%m-%d') AS `total_days`
 0
Author: Andrius Naruševičius, 2012-05-26 12:11:57