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

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

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

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

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

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

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

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

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

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

MySQL
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

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

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

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

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

Более подробное описание функции STR_TO_DATE и её аргументов можно посмотреть в справочнике.

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

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

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

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

Для этого в SQL есть следующие функции:

Отличие DATETIME от TIMESTAMP

В MySQL есть очень похожие друг на друга типы данных: DATETIME и TIMESTAMP. Они оба направлены на хранение даты и времени, но имеют ряд отличий, определяющих их целевое использование.

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

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

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

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

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

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

Соответственно, при изменении временной зоны все значения с типом TIMESTAMP будут выводиться с учётом текущей активной временной зоны.

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

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

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

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

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

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

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

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

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

И как же тогда корректно определять возраст? Для этого есть готовая встроенная функция — TIMESTAMPDIFF, которая первым аргументом принимает единицу измерения, в которой нужно вернуть разницу между двумя временными значениями.

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