Дата и время в SQL

Из всех типов данных в SQL временны́е данные являются наиболее сложными 🤯. Сложность возникает по нескольким причинам, и вот некоторые из них:

• множество способов задания даты и времени
• наличие временных зон
• неочевидность вычислений некоторых значений на основании временных данных. Например, сложность вычисления возраста.

Генерация временных данных

Временные данные можно получить одним из следующих способов:

• скопировать данные из существующего столбца с временны́м типом данных
• задать дату и время через строковое представление
• получить временны́е данные путём вызова встроенных функций, возвращающих временной тип данных

Строковое представление временных данных

Для задания даты и времени используются следующие форматы:

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

Примеры валидного задания временных значений через строковое представление:

MySQL 8.1
SELECT  CAST("2022-06-16 16:37:23" AS DATETIME) AS datetime_1,
        CAST("2014/02/22 16*37*22" AS DATETIME) AS datetime_2,
        CAST("20220616163723" AS DATETIME) AS datetime_3,
        CAST("2021-02-12" AS DATE) AS date_1,
        CAST("160:23:13" AS TIME) AS time_1,
        CAST("89" AS YEAR) AS year

MySQL 8.1
SELECT  CAST('2022-06-16 16:37:23' AS TIMESTAMP) AS timestamp_1,
        CAST('2014/02/22 16:37:22' AS TIMESTAMP) AS timestamp_2,
        CAST('20220616163723' AS TIMESTAMP) AS timestamp_3,
        CAST('2021-02-12' AS DATE) AS date_1,
        CAST('16:23:13' AS TIME) AS time_1

В запросе выше для принудительного преобразования строки в дату и время была использована функция CAST. Она необходима, если сервер не ожидает временного значения и, соответственно, автоматически не преобразует строку к нужному типу. С преобразованием типов мы более подробно познакомимся в статье «Функции преобразования типов, CAST».

Функции генерации дат

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

MySQL 8.1
SELECT STR_TO_DATE('November 13, 1998', '%M %d, %Y') AS date;

MySQL 8.1
SELECT TO_DATE('November 13, 1998', 'Month DD, YYYY') AS date;

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

MySQL 8.1
SELECT CURDATE(), CURTIME(), NOW();

MySQL 8.1
SELECT CURRENT_DATE, CURRENT_TIME, NOW();

Функции извлечения временных данных

Иногда необходимо получить не всю дату, а только её конкретную часть, например, месяц или год.

Отличие DATETIME от TIMESTAMP

Часовые пояса

Так как люди во всем мире хотят, чтобы полдень примерно соответствовал максимальному подъёму Солнца, то никогда не было задачи использовать универсальное время и мир был разделён на 24 часовых пояса.

В качестве точки отсчёта времени используется UTC (Coordinated Universal Time). Все остальные часовые пояса можно описать количеством часов сдвига от UTC. Для примера, часовой пояс Москвы может быть описан как UTC+3.

Часовой пояс является одной из настроек сервера баз данных и может задаваться:

• глобально
• для текущего пользователя
• для текущей пользовательской сессии

MySQL 8.1
SET GLOBAL time_zone = '+03:00';    // глобально
SET time_zone = '+03:00';           // для текущего пользователя
SET @@session.time_zone = '+03:00'; // для текущей пользовательской сессии

MySQL 8.1
ALTER DATABASE mydb SET timezone = 'Europe/Moscow';  // глобально для базы данных
ALTER USER myuser SET timezone = 'Europe/Moscow';    // для конкретного пользователя
SET TIME ZONE 'Europe/Moscow';                       // для текущей сессии
SET TIME ZONE '+03:00';                              // для текущей сессии

Примеры задач на дату и время

Хочется отдельно остановиться на наиболее популярных задачах, связанных с временным типом данных, на которых часто совершаются ошибки.

Определение возраста

При постановке задачи найти возраст человека по дате его рождения часто возникает соблазн 😈 вычислить разницу текущего года и года рождения человека:

MySQL 8.1
SELECT YEAR(NOW()) - YEAR('2003-07-03 14:10:26');

MySQL 8.1
SELECT EXTRACT(YEAR FROM NOW()) - EXTRACT(YEAR FROM TIMESTAMP '2003-07-03 14:10:26');

Проблема такого подхода в том, что он не учитывает был ли день рождения у данного человека в этом году или ещё нет. То есть, если на момент запроса уже наступило 3-е июля (07-03), то человек отпраздновал свой день рождения и ему уже 20 лет, иначе ему по-прежнему 19 года. Разница функций будет бесполезна — в обоих случаях она даст 20 лет.

Если определить возраст через разницу годов — неработающий вариант, то может возникнуть желание найти возраст через разницу дней между двумя датами, затем поделить эту разницу на количество дней в году и округлить вниз:

MySQL 8.1
SELECT FLOOR(DATEDIFF(NOW(), '2003-07-03 14:10:26') / 365);

MySQL 8.1
SELECT FLOOR(EXTRACT(DAY FROM NOW() - TIMESTAMP '2003-07-03 14:10:26') / 365);

И это решение будет гораздо точнее предыдущего. Но оно не будет абсолютно точным из-за наличия високосных годов, когда в году 366 дней. Хотя погрешность в вычислении возраста для 1 человека из-за наличия високосного года достаточно низкая, в вычислениях на определение, скажем, среднего возраста среди определённого списка людей, погрешность может накапливаться и исказить реальные значения.

И как же тогда корректно определять возраст?

MySQL 8.1
SELECT TIMESTAMPDIFF(YEAR, '2003-07-03 14:10:26', NOW());

MySQL 8.1
SELECT EXTRACT(YEAR FROM AGE(NOW(), TIMESTAMP '2003-07-03 14:10:26'));