MySQL / 5. Работа с датами и временем

Вычисления для високосных годов

Задача
Вам нужно выполнить вычисление даты, которое принимало бы во внимание наличие високосных годов. Например, длина месяца или года зависит от того, относится ли дата к високосному году.

Решение
Научитесь определять, является ли год високосным, и учтите результат этой проверки при выполнении вычислений.

Обсуждение
Вычисления, связанные с датами, осложняются тем, что не все месяцы содержат одинаковое количество дней, а 29-й день февраля, появляющийся только в високосном году – это вообще отдельная головная боль. В этом разделе рассказано о том, как определить, относится ли указанная дата к високосному году, а также как обрабатывать високосные годы при вычислении длины месяца или года.

Как определить, относится ли дата к високосному году
Чтобы узнать, относится ли дата d к високосному году, выделите составляющую года с помощью функции YEAR() и проверьте результат. Часто для проверки на високосный год применяют правило делимости на четыре, которое можно реализовать при помощи оператора деления по модулю:

YEAR(d) % 4 = 0

Однако такая проверка технически некорректна (например, 1900 год делится на четыре, но не является високосным). Для того чтобы год был високосным, он должен удовлетворять сразу двум условиям:

• Год должен делиться на четыре.
• Год не должен делиться на 100, если только он не делится и на 400.

Смысл второго ограничения в том, что год начала века не является високосным, за исключением каждого четвертого века. В SQL можно записать эти правила следующим образом:

(YEAR(d) % 4 = 0) AND ((YEAR(d) % 100 != 0) OR (YEAR(d) % 400 = 0))

Проверим правила високосности для данных таблицы date_val и получим такие результаты:

mysql> SELECT
-> d,
-> YEAR(d) % 4 = 0
-> AS "rule-of-thumb test",
-> (YEAR(d) % 4 = 0) AND ((YEAR(d) % 100 != 0) OR (YEAR(d) % 400 = 0))
-> AS "complete test"
-> FROM date_val;

+--------------+-----------------------+-----------------+
|           d        | rule-of-thumb test | complete test |
+--------------+-----------------------+-----------------+
| 1864-02-28 |             1                 |            1          |
| 1900-01-15 |             1                 |            0          |
| 1987-03-05 |             0                 |            0          |
| 1999-12-31 |             0                 |            0          |
| 2000-06-04 |             1                 |            1          |
+--------------+-----------------------+-----------------+

Как видите, полная проверка выводит иные результаты, нежели предыдущая. Первый тест некорректно обрабатывает 1900 год, второй же учитываетЕсли вы работаете со значениями дат в программе, то можете выполнять проверки на високосный год не на уровне SQL, а в своем языке API. Выделите первые четыре разряда строки даты для получения года и проверьте его.

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

В Perl и PHP проверка на високосный год имеет такой формат:

$year = substr ($date, 0, 4);
$is_leap = ($year % 4 == 0) && ($year % 100 != 0 || $year % 400 == 0);

Синтаксис Python аналогичен, но еще требуется операция преобразования типа:

year = int (date[0:4])
is_leap = (year % 4 == 0) and (year % 100 != 0 or year % 400 == 0)

Преобразование типа необходимо и в Java:

int year = Integer.valueOf (date.substring (0, 4)).intValue ();
boolean is_leap = (year % 4 == 0) && (year % 100 != 0 || year % 400 == 0);

Использование проверки на високосный год для вычисления длины года
Обычно в году 365 дней, но високосный год имеет один дополнительный день. Чтобы определить длину года, к которому относится указанная дата, можно провести только что описанную проверку на високосный год, чтобы знать, следует ли прибавлять лишний день:

$year = substr ($date, 0, 4);
$is_leap = ($year % 4 == 0) && ($year % 100 != 0 || $year % 400 == 0);
$days_in_year = ($is_leap ? 366 : 365);

Есть и другой способ вычисления длины года: можно вычислить дату последнего дня года и передать ее в функцию DAYOFYEAR():

mysql> SET @d = '2003-04-13';
mysql> SELECT DAYOFYEAR(DATE_FORMAT(@d,'%Y-12-31'));

+--------------------------------------------------------+
| DAYOFYEAR(DATE_FORMAT(@d,'%Y-12-31')) |
+--------------------------------------------------------+
|                                      365                                 |
+--------------------------------------------------------+

mysql> SET @d = '2004-04-13';
mysql> SELECT DAYOFYEAR(DATE_FORMAT(@d,'%Y-12-31'));

+--------------------------------------------------------+
| DAYOFYEAR(DATE_FORMAT(@d,'%Y-12-31')) |
+--------------------------------------------------------+
|                                       366                               |
+--------------------------------------------------------+

Использование проверки на високосный годдля вычисления длины месяца
Проверка на високосный год обеспечивает альтернативный способ достижения той же цели. Все месяцы, кроме февраля, имеют фиксированную длину, так что посмотрев на составляющую месяца указанной даты, вы можете сказать, какова длина этого месяца. Если вы знаете, относится ли дата к високосному году, то можете определить и длину февраля. SQL-выражение, вычисляющее количество дней месяца, можно записать так:

mysql> SELECT d,
-> ELT(MONTH(d),
-> 31,
-> IF((YEAR(d)%4 = 0) AND ((YEAR(d)%100 != 0) OR (YEAR(d)%400 = 0)),29,28),
-> 31,30,31,30,31,31,30,31,30,31)
-> AS 'days in month'
-> FROM date_val;

+--------------+------------------+
|           d        | days in month |
+--------------+------------------+
| 1864-02-28 |         29            |
| 1900-01-15 |         31            |
| 1987-03-05 |         31            |
| 1999-12-31 |         31            |
| 2000-06-04 |         30            |
+--------------+------------------+

Функция ELT() оценивает свой первый аргумент для определения значения n, затем возвращает n-е значение следующих аргументов. Все просто для всех месяцев, кроме февраля, для которого ELT() должна вернуть 29 или 28 в зависимости от того, является ли год високосным.

В языке API вы можете написать функцию, которая, получив в качестве аргумента дату в формате ISO, возвращала бы количество дней месяца, к которому относится дата. Приведем версию на Perl:

sub days_in_month
{
my $date = shift;
my $year = substr ($date, 0, 4);
my $month = substr ($date, 5, 2); # month, 1-based
my @days_in_month = (31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31);
my $days = $days_in_month[$month-1];
my $is_leap = ($year % 4 == 0) && ($year % 100 != 0 || $year % 400 == 0);
$days++ if $month == 2 && $is_leap; # add a day for Feb of leap years
return ($days);
}

Статьи по MySQL на эту тему:

Выбор записей по временным характеристикам
Вывод значений TIMESTAMP в удобном для чтения виде
Вычисления со значениями TIMESTAMP Задача
Использование значений TIMESTAMP
Обработка в MySQL строк как значений времени

Вернуться в раздел: MySQL / 5. Работа с датами и временем