В MySQL 5 є кілька типів даних для зберігання дати та часу. Це TIMESTAMP, DATE, DATETIME, TIME і YEAR. Всі вони мають свої особливості, і вибір на користь того чи іншого календарного типу повинен проводитися окремо в кожній конкретній ситуації. Я хотів би поділитися з вами результатом мого сьогоднішнього мініісследованія цих типів, в тому числі в аспекті роботи з тимчасовими зонами. Отже, всі календарні типи даних докладно описані в розділі «10.3. Date and Time Types »керівництва по MySQL. А важлива інформація, що стосується підтримки СУБД тимчасових зон, розписана в розділі «9.7. MySQL Server Time Zone Support ». Все наступне далі базується на вивченні керівництва. У той же час, в тут вказані лише нюанси вибору на користь того чи іншого типу, тому цей матеріал ніяк не замінює мануал, але доповнює його.
Спочатку коротка характеристика кожного з типів:
- TIMESTAMP - тип даних для зберігання дати та часу. Дані зберігаються у вигляді кількості секунд, що пройшли з початку «епохи Юнікса». Діапазон значень: 1970-01-01 00:00:00 - 2038-12-31 00:00:00. Займає 4 байти.
- YEAR - тип даних для зберігання року. Діапазон значень: 1901 - 2155. Займає 1 байт.
- DATE - тип даних для зберігання дати. Діапазон значень: 1000-01-01 - 9999-12-31. Займає 3 байта.
- TIME - тип даних для зберігання часу. Діапазон значень: -828: 59: 59 - 828: 59: 59. Займає 3 байта.
- DATETIME - тип даних для зберігання дати та часу. Діапазон значень: 1000-01-01 00:00:00 - 9999-12-31 00:00:00. Займає 8 байт.
Проаналізувавши опис типів, представлене вище, можна зробити практично все висновки про переваги і недоліки тих чи інших типів. Все досить просто і очевидно.
Використання календарних типів даний в MySQL
Почнемо з найпростішого - тип YEAR. Єдине його гідність - малий розмір - всього-то 1 байт. Але через це діє суворе обмеження по діапазону допустимих значень (тип може зберігати тільки 255 різних значень). Мені складно уявити практичну ситуацію, коли може знадобитися зберігати року строго в діапазоні від 1901 до 2155. Крім того, тип SMALLINT (2 байта) дає діапазон, достатній в більшості ситуацій для зберігання року. А економити 1 байт на рядку в таблиці БД в наш час сенсу немає.
Типи DATE і DATETIME можна об'єднати в одну групу. Вони зберігають дату або дату і час з досить широким діапазоном допустимих значень, незалежну від встановленої на сервері тимчасової зони. Їх використання безперечно має практичний сенс. Але якщо потрібно зберігати дати історичних подій, що йдуть в минуле за Нашу еру, доведеться вибрати інші типи даних. Для зберігання дат деяких подій, потенційно виходять за рамки діапазону типу TIMESTAMP (дні народжень, дати випуску продуктів, обрання президентів, запуски космічних ракет і т.д.), відмінно підійдуть ці типи. При використанні цих типів потрібно враховувати один важливий нюанс, але про це нижче.
Тип TIME можна використовувати для зберігання проміжку часу, коли не потрібна точність менше 1 секунди, і проміжки часу менше 829 годин. Додати тут більше нічого.
Залишився найцікавіший тип - TIMESTAMP. Розглядати його треба в порівнянні з DATE і DATETIME: TIMESTAMP теж призначений для зберігання дати та / або часу походження деяких подій. Важлива відмінність між ними в діапазонах значень: очевидно, що TIMESTAMP не годиться для зберігання історичних подій (навіть таких, як дні народжень), але відмінно підходить для зберігання поточних (логирование, дати розміщення статей, додавання товарів, оформлення замовлень) і майбутніх в доступному для огляду майбутньому подій (виходи нових версій, календарі та планувальники і т.д).
Основна зручність використання типу TIMESTAMP полягає в тому, що для стовпців цього типу в таблицях можна задавати значення за замовчуванням у вигляді підстановки поточного часу, а так само установки поточного часу при оновленні запису. Якщо вам потрібно ці можливості, то з імовірністю 99% TIMESTAMP - саме те, що вам потрібно. (Як етоделать, дивіться в мануалі.)
Не варто боятися того, що з наближенням до 2038 року ваш софт перестане працювати. По-перше, до цього часу вашим софтом, швидше за все, просто перестануть користуватися (особливо версіями, які пишуться зараз). По-друге, з наближенням до цієї дати розробники MySQL обов'язково щось придумають для збереження працездатності вашого софта. Все вирішиться так само добре, як проблема Y2K.
Отже, тип TIMESTAMP використовуємо для зберігання дат і часу звершення подій нашого часу, а DATETIME і DATE - для зберігання дат і часу звершення історичних подій, або подій глибокого майбутнього.
Діапазони значень - це важлива відмінність між типами TIMESTAMP, DATETIME і DATE, але не головне. Головне те, що TIMESTAMP зберігає значення в UTC. При збереженні значення воно перекладається з поточного часового поясу в UTC, а при його читанні - під час поточної тимчасової зони з UTC. DATETIME і DATE зберігають і виводять завжди одне і те ж час, незалежно від тимчасових зон.
Тимчасові зони встановлюються в СУБД MySQL глобально або для поточного підключення .Останній можна використовувати для забезпечення роботи різних користувачів в різних часових зонах на рівні СУБД. Всі значення часу фізично будуть зберігатися в UTC, а прийматися від клієнта і віддаватися Клінту - в значеннях його тимчасової зони. Але тільки при використанні типу даних TIMESTAMP. DATE і DATETIME завжди приймають, зберігають і віддають одне і те ж значення.
Функція NOW () і її синоніми повертають значення часу в поточній часовій зоні користувача.
Користувач X працює в зоні UTC / GMT + 2, Y - в зоні UTC / GMT +3. Для з'єднань користувачів з MySQL встановлена відповідна (у кожного своя) тимчасова зона. Користувач розміщує повідомлення на форумі, нас цікавить дата написання повідомлення.
Варіант 1: DATETIME. Користувач X пише повідомлення о 14:00 UTC / GMT +2. Значення в поле «дата» повідомлення підставляється як результат виконання функції NOW () - 14:00. Користувач Y зчитує час написання повідомлення і бачить ті ж 14:00. Але у нього в настройках стоітзона UTC / GMT + 3, і він думає, що повідомлення було написано не тільки що, а годину тому.
Варіант 2: TIMESTAMP. Користувач X пише повідомлення о 14:00 UTC / GMT +2. В поле «дата» потрапляє результат виконання функції NOW () - в даному випадку - 12:00 UTC / GMT + 0. ПользовательY зчитує час написання повідомлення і отримує (UTC / GMT + 3) (12:00 UTC / GMT + 0) = 15:00 UTC / GMT +3. Все виходить рівно так, як ми хочемо. І головне - користуватися цим вкрай зручно: для підтримки користувальницьких тимчасових зон не потрібно писати ніякої код приведення часу.
Можливості підстановки поточного часу і роботи з тимчасовими зонами в типі TIMESTAMP настільки вагомі, що якщо вам в якомусь балці треба зберігати дату без часу, все одно варто використовувати TIMESTAMP, замість DATE, що не економлячи 1 байт різниці між ними. При цьому на «00:00:00» просто не звертати уваги.