Автор:
RXL.
Дата написания: 4.08.2009.
Права на статью принадлежат автору и
Клубу программистов «Весельчак У».
На днях, на одном форуме по MySQL, прочел вопрос, который заинтересовал меня и привел к написанию данной статьи. Вопрос состоял в том, чтобы хранить смещение даты и в нужный момент суммировать его с некоторой датой. Попробуем по рассуждать на эту тему.
В документации по MySQL есть не мало функции работы с датой и временем. Рассмотрим возможности, касающиеся трансформации. Часть возможностей существуют в MySQL давно, а часть появилась в версиях 4.1.1 и 5.0.1.
Все встроенные функции трансформации даты не работают с неполными датами, когда месяц или день равны нулю, и возвращают NULL. Неполные даты выглядят так:
'2009-00-00'
'2009-00-31'
'2009-07-00'
Функции работы с датой — DATE_ADD() и DATE_SUB() — позволяют прибавлять к исходной дате и вычитать из нее временные интервалы в различных временных единицах: годах, кварталах, месяцах, неделях, днях, часах, минутах, секундах и в некоторых их комбинациях. В функции можно указать только один интервал. По естественным причинам среди них нет комбинаций года, квартала, месяца и недели с днями и более малыми единицами.
Подробнее об этом можно прочесть в MySQL Reference Manual. Некоторые возможности, такие как интервалы в кварталах и в неделях появились в MySQL 5.0.1.
Чуть гибче вычислять смещение даты можно, если использовать арифметику интервалов:
date + INTERVAL expr unit
date - INTERVAL expr unit
Эта форма позволяет строить составние выражения. Например:
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)
Очень полезная функция, вычисляющая последний день месяца. Функция появилась в версии 4.1.1. Она позволяет получить не только последний день месяца по указанной дате, но и вычислить первый день:
LAST_DAY(NOW()) + INTERVAL 1 DAY - INTERVAL 1 MONTH
Запись с множеством INTERVAL выглядит громоздко. Если она появляется в SQL-операторе один раз — не страшно, но если приходится использовать подобное выражение в операторе несколько раз, то легко запутаться.
Попробуем создать свои функции, позволяющие делать то, что не удобно или громоздко писать встроенными функциями.
Стандартный прием программирования — убрать громоздкое и многократно повторяющееся выражение в функцию.
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()— и понятно, и не длинно.
CREATE FUNCTION END_OF_MONTH(
p_date DATE
)
RETURNS DATE
DETERMINISTIC
CONTAINS SQL
SQL SECURITY INVOKER
RETURN LAST_DAY(p_date);
Встроенных функций, позволявших бы это вычислить, нет. Поэтому делаем сами.
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 позволяет их хранить в базе. Ведь неполная дата, в некотором смысле — интервал. Давайте напишем функцию суммирования полной даты с неполной.
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().
Проверяем:
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)
Выходит что-то непотребное: оба операнда становятся действительными числами и тупо суммируются:
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)
Все перечисленные здесь скрипты принадлежат автору и
Клубу программистов «Весельчак У». Я, как автор, не возражаю, если кто-либо будет их использовать в своих целях.
Вопросы о статье и по базам данных прошу задавать
на нашем форуме — «Весельчак У».
Для удобства отладки, рекомендую пару строк кода.
Выводит все функции в схеме «test»:
SELECT ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA = 'test'
AND ROUTINE_TYPE = 'FUNCTION';
Генерит код для удаления всех функций в схеме «test»:
SELECT GROUP_CONCAT(CONCAT('DROP FUNCTION ', ROUTINE_NAME, ';') SEPARATOR '')
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA = 'test'
AND ROUTINE_TYPE = 'FUNCTION';