Статья
Версия для печати
Обсудить на форуме
MySQL. Преобразование дат.


Автор: RXL.
Дата написания: 4.08.2009.
Права на статью принадлежат автору и Клубу программистов «Весельчак У».

Содержание.


Предисловие.

На днях, на одном форуме по MySQL, прочел вопрос, который заинтересовал меня и привел к написанию данной статьи. Вопрос состоял в том, чтобы хранить смещение даты и в нужный момент суммировать его с некоторой датой. Попробуем по рассуждать на эту тему.

Встроенные возможности MySQL.

В документации по MySQL есть не мало функции работы с датой и временем. Рассмотрим возможности, касающиеся трансформации. Часть возможностей существуют в MySQL давно, а часть появилась в версиях 4.1.1 и 5.0.1.
Все встроенные функции трансформации даты не работают с неполными датами, когда месяц или день равны нулю, и возвращают NULL. Неполные даты выглядят так:
Код:
'2009-00-00'
'2009-00-31'
'2009-07-00'

Функция DATE_ADD().

Функции работы с датой — DATE_ADD() и DATE_SUB() — позволяют прибавлять к исходной дате и вычитать из нее временные интервалы в различных временных единицах: годах, кварталах, месяцах, неделях, днях, часах, минутах, секундах и в некоторых их комбинациях. В функции можно указать только один интервал. По естественным причинам среди них нет комбинаций года, квартала, месяца и недели с днями и более малыми единицами.
Подробнее об этом можно прочесть в MySQL Reference Manual. Некоторые возможности, такие как интервалы в кварталах и в неделях появились в MySQL 5.0.1.

Арифметика интервалов.

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

Код: (MySQL)
date + INTERVAL expr unit
date - INTERVAL expr unit

Эта форма позволяет строить составние выражения. Например:

Код: (MySQL)
NOW() - INTERVAL 1 MONTH + INTERVAL 3 HOUR

При работе с интервалами из месяцев и кварталов следует помнить, что в месяцах разное количество дней и при вычислении возможно «округление» для месяца к последнему дню месяца результата:

mysql> SELECT '2009-07-31' - INTERVAL 1 MONTH;
+---------------------------------+
| '2009-07-31' - INTERVAL 1 MONTH |
+---------------------------------+
| 2009-06-30                      |
+---------------------------------+
1 row in set (0.00 sec)

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

mysql> SELECT '2009-07-31' - INTERVAL 1 DAY - INTERVAL 1 MONTH;
+--------------------------------------------------+
| '2009-07-31' - INTERVAL 1 DAY - INTERVAL 1 MONTH |
+--------------------------------------------------+
| 2009-06-30                                       |
+--------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT '2009-07-31' - INTERVAL 1 MONTH - INTERVAL 1 DAY;
+--------------------------------------------------+
| '2009-07-31' - INTERVAL 1 MONTH - INTERVAL 1 DAY |
+--------------------------------------------------+
| 2009-06-29                                       |
+--------------------------------------------------+
1 row in set (0.00 sec)

Функция LAST_DAY().

Очень полезная функция, вычисляющая последний день месяца. Функция появилась в версии 4.1.1. Она позволяет получить не только последний день месяца по указанной дате, но и вычислить первый день:

Код: (MySQL)
LAST_DAY(NOW()) + INTERVAL 1 DAY - INTERVAL 1 MONTH

Запись с множеством INTERVAL выглядит громоздко. Если она появляется в SQL-операторе один раз — не страшно, но если приходится использовать подобное выражение в операторе несколько раз, то легко запутаться.

Исправляем и расширяем сами.

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

Первый день месяца.

Стандартный прием программирования — убрать громоздкое и многократно повторяющееся выражение в функцию.

Код: (MySQL)
CREATE FUNCTION BEGIN_OF_MONTH(
  p_date DATE
)
  RETURNS DATE
  DETERMINISTIC
  CONTAINS SQL
  SQL SECURITY INVOKER
RETURN LAST_DAY(p_date) + INTERVAL 1 DAY - INTERVAL 1 MONTH;

Вроде как стало проще, компактнее и понятнее:

mysql> SELECT BEGIN_OF_MONTH('2009-07-31');
+------------------------------+
| BEGIN_OF_MONTH('2009-07-31') |
+------------------------------+
| 2009-07-01                   |
+------------------------------+
1 row in set (0.00 sec)

Честно говоря, не понимаю, зачем было сокращать LAST_DAY_OF_MONTH() до LAST_DAY() — это не очевидно. Я бы вообще назвал это END_OF_MONTH()— и понятно, и не длинно.

Код: (MySQL)
CREATE FUNCTION END_OF_MONTH(
  p_date DATE
)
  RETURNS DATE
  DETERMINISTIC
  CONTAINS SQL
  SQL SECURITY INVOKER
RETURN LAST_DAY(p_date);

Первый и последний день года, квартала или недели.

Встроенных функций, позволявших бы это вычислить, нет. Поэтому делаем сами.

Код: (MySQL)
CREATE FUNCTION BEGIN_OF_YEAR(
  p_date DATE
)
  RETURNS DATE
  DETERMINISTIC
  CONTAINS SQL
  SQL SECURITY INVOKER
RETURN MAKEDATE(YEAR(p_date), 1);

CREATE FUNCTION END_OF_YEAR(
  p_date DATE
)
  RETURNS DATE
  DETERMINISTIC
  CONTAINS SQL
  SQL SECURITY INVOKER
RETURN MAKEDATE(YEAR(p_date) + 1, 1) - INTERVAL 1 DAY;

CREATE FUNCTION BEGIN_OF_WEEK(
  p_date DATE
)
  RETURNS DATE
  DETERMINISTIC
  CONTAINS SQL
  SQL SECURITY INVOKER
RETURN p_date - INTERVAL WEEKDAY(p_date) DAY;

Функции END_OF_WEEK(), BEGIN_OF_QUARTER() и END_OF_QUARTER() не расписываю — не трудно догадаться, как они выглядят. Код функций можно посмотреть в приложении к статье.

Суммирование неполных дат.

Хотя встроенные функции и не позволяют оперировать неполными датами, MySQL позволяет их хранить в базе. Ведь неполная дата, в некотором смысле — интервал. Давайте напишем функцию суммирования полной даты с неполной.

Код: (MySQL)
CREATE FUNCTION DATE_ADD_OFFSET(
  p_date DATETIME,
  p_offset DATETIME
)
  RETURNS DATETIME
  DETERMINISTIC
  CONTAINS SQL
  SQL SECURITY INVOKER
RETURN p_date
  + INTERVAL YEAR(p_offset) YEAR
  + INTERVAL MONTH(p_offset) MONTH
  + INTERVAL DAY(p_offset) DAY
  + INTERVAL MAKETIME(
    HOUR(p_offset),
    MINUTE(p_offset),
    SECOND(p_offset)
  ) HOUR_SECOND;

Аналогично выглядит обратная функция — DATE_SUB_OFFSET().

Интересные аспекты:

Преобразование неполной DATE в DATETIME проходит без ошибок.

Проверяем:

Код: (MySQL)
CREATE FUNCTION DATE_TO_DATETIME(
  p_date DATE
)
  RETURNS DATETIME
  DETERMINISTIC
  CONTAINS SQL
  SQL SECURITY INVOKER
RETURN p_date;

mysql> select DATE_TO_DATETIME('2009-07-00');
+--------------------------------+
| DATE_TO_DATETIME('2009-07-00') |
+--------------------------------+
| 2009-07-00 00:00:00            |
+--------------------------------+
1 row in set (0.00 sec)

Нельзя суммировать DATE с TIME или числом.

Выходит что-то непотребное: оба операнда становятся действительными числами и тупо суммируются:

mysql> SELECT NOW(), NOW() + 0, NOW() + MAKETIME(0, 0, 59);
+---------------------+-----------------------+----------------------------+
| NOW()               | NOW() + 0             | NOW() + MAKETIME(0, 0, 59) |
+---------------------+-----------------------+----------------------------+
| 2009-08-02 23:49:38 | 20090802234938.000000 |      20090802234997.000000 |
+---------------------+-----------------------+----------------------------+
1 row in set (0.00 sec)


Узнаем дату дня недели.

Узнать день недели по дате легко — использовать встроенную функцию WEEKDAY(). Другое дело узнать, на какую дату приходится пятница второй недели или последняя суббота месяца.
Код функции FIND_WEEK_DAY() находится в приложении. Первый параметр — дата (указывает на месяц), второй — номер недели, третий — день недели (в той же нумерации дней, что и WEEKDAY()). Проверим ее.

Положительное значение недели используется для поиска с начала указанного месяца и не ограничивается самим месяцем — можно даже найти тринадцатое воскресение лета.
Первое воскресение:

mysql> select FIND_WEEK_DAY('2009-08-03', 1, 6);
+-----------------------------------+
| FIND_WEEK_DAY('2009-08-03', 1, 6) |
+-----------------------------------+
| 2009-08-02 00:00:00               |
+-----------------------------------+
1 row in set, 2 warnings (0.00 sec)

Отрицательное значение недели используется для обратного поиска с конца указанного месяца и так же не лимитируется одним месяцем.
Предпоследняя пятница:

mysql> select FIND_WEEK_DAY('2009-08-03', -2, 4);
+------------------------------------+
| FIND_WEEK_DAY('2009-08-03', -2, 4) |
+------------------------------------+
| 2009-08-28 00:00:00                |
+------------------------------------+
1 row in set, 2 warnings (0.00 sec)

Нулевое значение недели используется для поиска следующего нужного дня недели от указанной даты.
Следующий понедельник:

mysql> select FIND_WEEK_DAY('2009-08-03', 0, 0);
+-----------------------------------+
| FIND_WEEK_DAY('2009-08-03', 0, 0) |
+-----------------------------------+
| 2009-08-10 00:00:00               |
+-----------------------------------+
1 row in set, 2 warnings (0.00 sec)

Была еще идея сделать обратный поиск с указанной даты, но руки не дошли.

Множественные преобразования в одной функции.

Как сделать множественные преобразования даты и времени, не городя кучу вычислений?
Видимо, надо написать эту кучу вычислений и написать функцию, котороя бы понимала, что мы от нее хотим, и выполняла бы эту кучу вычислений.
Все же из соображения гибкости, читаемости и производительности выделим самостоятельные функциональные части в функции DATE_RANGE() и DATE_ADD_INTERVAL(), а обработку последовательности правил поместим в DATE_TRANSFORM(). Код, ввиду громоздкости, здесь не привожу — смотрите приложение.

Для DATE_TRANSFORM() формат правила преобразования:

Код:
([YMQWDhms]-?[0-9]+|[BE][YQMWDhm])

Правила записываются без разделителей и выполняются последовательно.
Для лучшего понимания приведу ряд примеров.

Первый день следующего месяца. Вычисляю как: следующий месяц, первый день месяца:

mysql> SELECT DATE_TRANSFORM('2009-07-09', 'M1BM') 'Next invoice day';
+---------------------+
| Next invoice day    |
+---------------------+
| 2009-08-01 00:00:00 |
+---------------------+
1 row in set (0.00 sec)

Девять утра третьего дня второго месяца предыдущего квартала. Только представьте себе, как выглядела бы эта формула с использованием INTERVAL и встроенных функций! У нас же все намного проще:

mysql> SELECT DATE_TRANSFORM(NOW(), 'Q-1BQM1D2H9') ':)';
+---------------------+
| :)                  |
+---------------------+
| 2009-05-03 09:00:00 |
+---------------------+
1 row in set (0.00 sec)

Приложение.

SQL-скрипты, использованные в статье.

Все перечисленные здесь скрипты принадлежат автору и Клубу программистов «Весельчак У». Я, как автор, не возражаю, если кто-либо будет их использовать в своих целях.


Наш форум.

Вопросы о статье и по базам данных прошу задавать на нашем форуме — «Весельчак У».

Некоторые полезные в отладке запросы.

Для удобства отладки, рекомендую пару строк кода.

Выводит все функции в схеме «test»:

Код: (MySQL)
SELECT ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA = 'test'
  AND ROUTINE_TYPE = 'FUNCTION';

Генерит код для удаления всех функций в схеме «test»:

Код: (MySQL)
SELECT GROUP_CONCAT(CONCAT('DROP FUNCTION ', ROUTINE_NAME, ';') SEPARATOR '')
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA = 'test'
  AND ROUTINE_TYPE = 'FUNCTION';

Версия для печати
Обсудить на форуме