Занятие 10. Особенности работы с датой и временем

📁 Блок: SQL / MySQL ⏱️ Время изучения: ~60 мин 🎯 Сложность: Начальная

#date #datetime #timestamp #time #year #now #curdate #curtime #date_format #datediff #date_add #date_sub #extract #from #month #day #time_to_sec #sec_to_time

⚡ Кратко: суть темы

Занятие учит работать с датой и временем в SQL. Разбираем типы данных DATE, TIME, DATETIME, TIMESTAMP, YEAR и основные функции для форматирования, извлечения частей и арифметики с датами.

Выбирайте тип данных под задачу: только дата — DATE, дата и время — DATETIME, момент времени с часовым поясом — TIMESTAMP.
DATE_FORMAT, YEAR, MONTH, EXTRACT — для получения нужной части даты.
DATE_ADD, DATE_SUB, DATEDIFF — для вычислений с датами.

Что запомнить: даты — это не строки; используйте специальные функции, а не конкатенацию срезами.

Частая ошибка: сравнивать даты как строки или игнорировать часовой пояс TIMESTAMP.

📖 Типы данных для даты и времени в MySQL

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

Тип	Формат	Диапазон	Когда использовать
`DATE`	`YYYY-MM-DD`	1000-01-01 … 9999-12-31	День рождения, дата заказа, дедлайн без времени
`DATETIME`	`YYYY-MM-DD HH:MM:SS`	1000-01-01 00:00:00 … 9999-12-31 23:59:59	Локальные события: создание записи, встреча
`TIMESTAMP`	`YYYY-MM-DD HH:MM:SS`	1970-01-01 00:00:01 UTC … 2038-01-19 03:14:07 UTC	Момент времени в разных часовых поясах; автообновление
`TIME`	`HH:MM:SS`	-838:59:59 … 838:59:59	Продолжительность, время суток
`YEAR`	`YYYY`	1901 … 2155	Год выпуска, учебный год

Особенности `TIMESTAMP`

Хранит значение в формате UTC и конвертирует при выводе в часовой пояс сессии.
Поддерживает автоматическое заполнение текущим временем и автообновление при изменении строки.
Диапазон ограничен 2038 годом из-за 32-битного UNIX-времени (проблема Year 2038).

-- Автоматическое заполнение времени создания и обновления
CREATE TABLE posts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

📖 Функции для работы с датами и временем

Получение текущего момента

Функция	Что возвращает	Пример
`NOW()`	Текущие дата и время	`SELECT NOW();`
`CURDATE()`	Текущая дата	`SELECT CURDATE();`
`CURTIME()`	Текущее время	`SELECT CURTIME();`

Форматирование и извлечение частей

Функция	Назначение	Пример
`DATE_FORMAT(date, format)`	Форматирует дату по шаблону	`SELECT DATE_FORMAT(NOW(), '%d-%m-%Y');`
`YEAR(date)`, `MONTH(date)`, `DAY(date)`	Извлекают год, месяц, день	`SELECT YEAR(order_date) FROM orders;`
`EXTRACT(unit FROM date)`	Извлекает любую часть даты	`SELECT EXTRACT(YEAR FROM NOW());`

Арифметика с датами

Функция	Назначение	Пример
`DATEDIFF(date1, date2)`	Разница в днях (`date1 - date2`)	`SELECT DATEDIFF('2024-08-30', '2024-08-25');`
`DATE_ADD(date, INTERVAL value unit)`	Прибавляет интервал	`SELECT DATE_ADD(NOW(), INTERVAL 10 DAY);`
`DATE_SUB(date, INTERVAL value unit)`	Вычитает интервал	`SELECT DATE_SUB(NOW(), INTERVAL 90 DAY);`
`TIME_TO_SEC(time)`	Время в секунды	`SELECT TIME_TO_SEC('02:30:00');`
`SEC_TO_TIME(seconds)`	Секунды во время	`SELECT SEC_TO_TIME(9000);`

📖 Различия MySQL и PostgreSQL

Разные СУБД используют разный синтаксис для одних и тех же операций. Ниже — ключевые отличия, с которыми стоит быть знакомым.

Операция	MySQL	PostgreSQL
Текущая дата и время	`NOW()`, `CURRENT_TIMESTAMP()`	`NOW()`, `CURRENT_TIMESTAMP`
Текущая дата	`CURDATE()`	`CURRENT_DATE`
Текущее время	`CURTIME()`	`CURRENT_TIME`
Разница в днях	`DATEDIFF(date1, date2)`	`DATE_PART('day', date1 - date2)`
Добавить интервал	`DATE_ADD(date, INTERVAL 7 DAY)`	`date + INTERVAL '7 days'`
Вычесть интервал	`DATE_SUB(date, INTERVAL 7 DAY)`	`date - INTERVAL '7 days'`
Извлечь год	`YEAR(date)`	`EXTRACT(YEAR FROM date)`
Время в секунды	`TIME_TO_SEC(time)`	`EXTRACT(EPOCH FROM time)`

Проверить по документации: синтаксис работы с датами существенно различается между СУБД. Перед переносом запроса из MySQL в PostgreSQL или наоборот сверяйтесь с актуальной документацией.

✨ Современные практики

Храните даты в DATETIME, если часовой пояс не важен, и в TIMESTAMP, если важен.
Используйте DATE_FORMAT только для вывода; не храните даты как строки.
Для расчёта возраста или длительности предпочитайте функции разницы (DATEDIFF, TIMESTAMPDIFF) вместо ручного вычитания строк.
Проверяйте часовой пояс сервера перед работой с TIMESTAMP.