MySQL: Временные интервалы и арифметика с датами

Временные интервалы в БД MySQL имеют такой синтаксис:

INTERVAL expr unit

Где expr представляет количество. unit представляет собой единицу интерпретации количества; это спецификатор, такой как HOUR, DAY или WEEK. Ключевое слово INTERVAL и спецификатор единиц измерения не чувствительны к регистру.

В следующей таблице показана ожидаемая форма аргумента expr для каждого значения единицы измерения unit.

Выражение временного интервала и аргументы единиц измерения:

MySQL разрешает любой разделитель в формате expr. В таблице показаны рекомендуемые разделители.

Временные интервалы используются для определенных функций, таких как DATE_ADD() и DATE_SUB():

mysql> SELECT DATE_ADD('2018-05-01',INTERVAL 1 DAY);
        -> '2018-05-02'
mysql> SELECT DATE_SUB('2018-05-01',INTERVAL 1 YEAR);
        -> '2017-05-01'
mysql> SELECT DATE_ADD('2020-12-31 23:59:59',
    ->                 INTERVAL 1 SECOND);
        -> '2021-01-01 00:00:00'
mysql> SELECT DATE_ADD('2018-12-31 23:59:59',
    ->                 INTERVAL 1 DAY);
        -> '2019-01-01 23:59:59'
mysql> SELECT DATE_ADD('2100-12-31 23:59:59',
    ->                 INTERVAL '1:1' MINUTE_SECOND);
        -> '2101-01-01 00:01:00'
mysql> SELECT DATE_SUB('2025-01-01 00:00:00',
    ->                 INTERVAL '1 1:1:1' DAY_SECOND);
        -> '2024-12-30 22:58:59'
mysql> SELECT DATE_ADD('1900-01-01 00:00:00',
    ->                 INTERVAL '-1 10' DAY_HOUR);
        -> '1899-12-30 14:00:00'
mysql> SELECT DATE_SUB('1998-01-02', INTERVAL 31 DAY);
        -> '1997-12-02'
mysql> SELECT DATE_ADD('1992-12-31 23:59:59.000002',
    ->            INTERVAL '1.999999' SECOND_MICROSECOND);
        -> '1993-01-01 00:00:01.000001'

с датами и временем также может выполняться в выражениях с использованием ключевого слова INTERVAL вместе с оператором + или -:

date + INTERVAL expr unit
date - INTERVAL expr unit

Выражение INTERVAL expr unit разрешен с любой стороны оператора +, если выражение с другой стороны является значением даты или даты и времени. Для оператора - выражение INTERVAL expr unit разрешено только с правой стороны, потому что нет смысла вычитать дату или значение даты и времени из интервала.

mysql> SELECT '2018-12-31 23:59:59' + INTERVAL 1 SECOND;
        -> '2019-01-01 00:00:00'
mysql> SELECT INTERVAL 1 DAY + '2018-12-31';
        -> '2019-01-01'
mysql> SELECT DATE_ADD('2022-10-01', INTERVAL 3 MONTH) + INTERVAL 10 DAY;
        -> '2023-01-11'
mysql> SELECT '2025-01-01' - INTERVAL 1 SECOND;
        -> '2024-12-31 23:59:59'

Функция EXTRACT() использует те же спецификаторы единиц измерения, что и DATE_ADD() или DATE_SUB(), но извлекает части из даты, а не выполняет арифметические операции с датами:

mysql> SELECT EXTRACT(YEAR FROM '2019-07-02');
        -> 2019
mysql> SELECT EXTRACT(YEAR_MONTH FROM '2019-07-02 01:02:03');
        -> 201907

Временные интервалы можно использовать в операторах CREATE EVENT:

CREATE EVENT myevent
    ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
    DO
      UPDATE myschema.mytable SET mycol = mycol + 1;

Если указать слишком короткое значение интервала (не включает все части интервала, которые можно было бы ожидать от ключевого слова unit), то MySQL предполагает, что крайние левые части значения интервала пропущены. Например, если указать единицу DAY_SECOND, ожидается, что значение expr будет состоять из дней, часов, минут и секунд. Если указать значение, подобное '1:10', то MySQL предполагает, что дни и часы отсутствуют, а значение представляет минуты и секунды. Другими словами, '1:10' DAY_SECOND интерпретируется таким образом, что это эквивалентно '1:10' MINUTE_SECOND. Это аналогично тому, как MySQL интерпретирует значения TIME как представляющие прошедшее время, а не как время суток.

Выражение expr обрабатывается как строка, поэтому надо быть осторожным при указании не строкового значения с помощью INTERVAL. Например, со спецификатором интервала HOUR_MINUTE '6/4' обрабатывается как 6 часов 4 минуты, тогда как 6/4 оценивается как 1,5000 и обрабатывается как 1 час 5000 минут:

mysql> SELECT '6/4', 6/4;
        -> 1.5000
mysql> SELECT DATE_ADD('2019-01-01', INTERVAL '6/4' HOUR_MINUTE);
        -> '2019-01-01 06:04:00'
mysql> SELECT DATE_ADD('2019-01-01', INTERVAL 6/4 HOUR_MINUTE);
        -> '2019-01-04 12:20:00'

Чтобы обеспечить правильную интерпретацию значения интервала, можно использовать операцию CAST(). Чтобы рассматривать 6/4 как 1 час 5 минут, нужно привести его к значению DECIMAL с одной дробной цифрой:

mysql> SELECT CAST(6/4 AS DECIMAL(3,1));
        -> 1.5
mysql> SELECT DATE_ADD('1970-01-01 12:00:00',
    ->                 INTERVAL CAST(6/4 AS DECIMAL(3,1)) HOUR_MINUTE);
        -> '1970-01-01 13:05:00'

Если добавляете или вычитаете из значения даты что-то, что содержит часть времени, то результат автоматически преобразуется в значение даты и времени:

mysql> SELECT DATE_ADD('2023-01-01', INTERVAL 1 DAY);
        -> '2023-01-02'
mysql> SELECT DATE_ADD('2023-01-01', INTERVAL 1 HOUR);
        -> '2023-01-01 01:00:00'

Если добавить MONTH, YEAR_MONTH или YEAR, и полученная дата имеет день, то превышающий максимальный день для нового месяца, день корректируется до максимального количества дней в новом месяце:

mysql> SELECT DATE_ADD('2019-01-30', INTERVAL 1 MONTH);
        -> '2019-02-28'

Арифметические операции с датами требуют полных дат и не работают с неполными датами, такими как '2016-07-00', или неправильно сформированными датами:

mysql> SELECT DATE_ADD('2016-07-00', INTERVAL 1 DAY);
        -> NULL
mysql> SELECT '2005-03-32' + INTERVAL 1 MONTH;
        -> NULL