Днями, на одному форумі з MySQL, прочитав питання, який зацікавив мене і привів до написання даної статті. Питання полягало в тому, щоб зберігати зміщення дати і в потрібний момент підсумувати його з деякою датою. Спробуємо по міркувати на цю тему.
Вбудовані можливості MySQL.
У документації по MySQL є багато функції роботи з датою і часом. Розглянемо можливості, що стосуються трансформації. Частина можливостей існують в MySQL давно, а частина з'явилася в версіях 4.1.1 і 5.0.1.
Всі вбудовані функції трансформації дати не працюють з неповними датами, коли місяць або день дорівнюють нулю, і повертають NULL. Неповні дати виглядають так:
Функції роботи з датою - DATE_ADD () і DATE_SUB () - дозволяють додавати до вихідної датою і віднімати з неї тимчасові інтервали в різних часових одиницях: роках, кварталах, місяцях, тижнях, днях, годинах, хвилинах, секундах і в деяких їх комбінаціях. У функції можна вказати тільки один інтервал. З природних причин серед них немає комбінацій року, кварталу, місяця і тижня з днями і меншими одиницями.
Трохи гнучкіше обчислювати зсув дати можна, якщо використовувати арифметику інтервалів:
date + INTERVAL expr unit
date - INTERVAL expr unit
Ця форма дозволяє будувати складові виразу. наприклад:
NOW # 40; # 41; - INTERVAL 1 MONTH + INTERVAL 3 HOUR
При роботі з інтервалами з місяців і кварталів слід пам'ятати, що в місцях різна кількість днів і при обчисленні можливо «округлення» для місяця до останнього дня місяця результату:
Ще слід пам'ятати, що в складових виразах важливий порядок операцій - правила арифметичного підсумовування тут не застосовні. Наприклад, такі команди призводять до різного результату:
Дуже корисна функція, що обчислює останній день місяця. Функція з'явилася у версії 4.1.1. Вона дозволяє отримати не тільки останній день місяця за вказаною датою, а й обчислити перший день:
LAST_DAY # 40; NOW # 40; # 41; # 41; + INTERVAL 1 DAY - INTERVAL 1 MONTH
Запис з безліччю INTERVAL виглядає громіздко. Якщо вона з'являється в SQL-операторі один раз - не страшно, але якщо доводиться використовувати подібний вираз в операторі кілька разів, то легко заплутатися.
Виправляємо і розширюємо самі.
Спробуємо створити свої функції, що дозволяють робити те, що не зручно або громіздко писати вбудованими функціями.
Перший день місяця.
Стандартний прийом програмування - прибрати громіздке і багато разів повторюється вислів в функцію.
CREATE FUNCTION BEGIN_OF_MONTH # 40;
p_date DATE
# 41;
RETURNS DATE
DETERMINISTIC
CONTAINS SQL
SQL SECURITY INVOKER
RETURN LAST_DAY # 40; p_date # 41; + INTERVAL 1 DAY - INTERVAL 1 MONTH;
Ніби як стало простіше, компактніше і зрозуміліше:
Чесно кажучи, не розумію, навіщо було скорочувати LAST_DAY_OF_MONTH () до LAST_DAY () - це не очевидно. Я б взагалі назвав це END_OF_MONTH () - і зрозуміло, і не довго.
CREATE FUNCTION END_OF_MONTH # 40;
p_date DATE
# 41;
RETURNS DATE
DETERMINISTIC
CONTAINS SQL
SQL SECURITY INVOKER
RETURN LAST_DAY # 40; p_date # 41; ;
Перший і останній день року, кварталу або тижні.
Вбудованих функцій, які давали можливість б це обчислити, немає. Тому робимо самі.
CREATE FUNCTION BEGIN_OF_YEAR # 40;
p_date DATE
# 41;
RETURNS DATE
DETERMINISTIC
CONTAINS SQL
SQL SECURITY INVOKER
RETURN MAKEDATE # 40; YEAR # 40; p_date # 41 ;. 1 # 41; ;
CREATE FUNCTION END_OF_YEAR # 40;
p_date DATE
# 41;
RETURNS DATE
DETERMINISTIC
CONTAINS SQL
SQL SECURITY INVOKER
RETURN MAKEDATE # 40; YEAR # 40; p_date # 41; + 1. 1 # 41; - INTERVAL 1 DAY;
CREATE FUNCTION BEGIN_OF_WEEK # 40;
p_date DATE
# 41;
RETURNS DATE
DETERMINISTIC
CONTAINS SQL
SQL SECURITY INVOKER
RETURN p_date - INTERVAL WEEKDAY # 40; p_date # 41; DAY;
Функції END_OF_WEEK (). BEGIN_OF_QUARTER () і END_OF_QUARTER () не розписувалися - не важко здогадатися, як вони виглядають. Код функцій можна подивитися в додатку до статті.
Підсумовування неповних дат.
Хоча вбудовані функції і не дозволяють оперувати неповними датами, MySQL дозволяє їх зберігати в базі. Адже неповна дата, в деякому сенсі - інтервал. Давайте напишемо функцію підсумовування повної дати з неповною.
CREATE FUNCTION DATE_ADD_OFFSET # 40;
p_date DATETIME.
p_offset DATETIME
# 41;
RETURNS DATETIME
DETERMINISTIC
CONTAINS SQL
SQL SECURITY INVOKER
RETURN p_date
+ INTERVAL YEAR # 40; p_offset # 41; YEAR
+ INTERVAL MONTH # 40; p_offset # 41; MONTH
+ INTERVAL DAY # 40; p_offset # 41; DAY
+ INTERVAL MAKETIME # 40;
HOUR # 40; p_offset # 41 ;.
MINUTE # 40; p_offset # 41 ;.
SECOND # 40; p_offset # 41;
# 41; HOUR_SECOND;
Аналогічно виглядає зворотна функція - DATE_SUB_OFFSET ().
Перетворення неповної DATE в DATETIME проходить без помилок.
CREATE FUNCTION DATE_TO_DATETIME # 40;
p_date DATE
# 41;
RETURNS DATETIME
DETERMINISTIC
CONTAINS SQL
SQL SECURITY INVOKER
RETURN p_date;
Не можна підсумувати DATE з TIME або числом.
Виходить щось непотрібне: обидва операнда стають дійсними числами і тупо підсумовуються:
Дізнаємося дату дня тижня.
Дізнатися день тижня за датою легко - використовувати вбудовану функцію WEEKDAY (). Інша справа дізнатися, на яку дату припадає п'ятницю другий тижні або остання субота місяця.
Код функції FIND_WEEK_DAY () знаходиться в додатку. Перший параметр - дата (вказує на місяць), другий - номер тижня, третій - день тижня (в тій же нумерації днів, що і WEEKDAY ()). Перевіримо її.
Позитивне значення тижні використовується для пошуку з початку зазначеного місяця і не обмежується самим місяцем - можна навіть знайти тринадцятого воскресіння літа.
Негативне значення тижні використовується для зворотного пошуку з кінця зазначеного місяця і так само не лімітується одним місяцем.
Нульове значення тижні використовується для пошуку наступного потрібного дня тижня від зазначеної дати.
Була ще ідея зробити зворотний пошук із зазначеної дати, але руки не дійшли.
Множинні перетворення в одній функції.
Як зробити множинні перетворення дати і часу, ні міста купу обчислень?
Мабуть, треба написати цю купу обчислень і написати функцію, котороя б розуміла, що ми від неї хочемо, і виконувала б цю купу обчислень.
Все ж з міркування гнучкості, читання і продуктивності виділимо самостійні функціональні частини в функції DATE_RANGE () і DATE_ADD_INTERVAL (). а обробку послідовності правил помістимо в DATE_TRANSFORM (). Код, з огляду на громіздкість, тут не наводжу - дивіться додаток.
Для DATE_TRANSFORM () формат правила перетворення:
Правила записуються без роздільників і виконуються послідовно.
Для кращого розуміння наведу ряд прикладів.
Перший день наступного місяця. Обчислюю як: наступний місяць, перший день місяця:
Дев'ята ранку третього дня другого місяця попереднього кварталу. Тільки уявіть собі, як виглядала б ця формула з використанням INTERVAL і вбудованих функцій! У нас же все набагато простіше:
SQL-скрипти, використані в статті.
- Набір функцій для отримання початку і кінця діапазону.
- Початок і кінець інтервалу для типу DATETIME - функція DATE_RANGE ().
- Пошук дня тижня - функція FIND_WEEK_DAY ().
- Скорочений спосіб арифметики інтервалів - функція DATE_ADD_INTERVAL ().
- Складні перетворення типу DATETIME - функція DATE_TRANSFORM ().
Питання про статтю і по базах даних прошу задавати на нашому форумі - «Веселун У».
Деякі корисні в налагодженні запити.
Для зручності налагодження, рекомендую пару рядків коду.
Виводить всі функції в схемі «test»:
SELECT ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA = 'test'
AND ROUTINE_TYPE = 'FUNCTION';
Генерує код для видалення всіх функцій в схемі «test»:
SELECT GROUP_CONCAT # 40; CONCAT # 40; 'DROP FUNCTION'. ROUTINE_NAME. ';' # 41; SEPARATOR '' # 41;
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA = 'test'
AND ROUTINE_TYPE = 'FUNCTION';