Операции с датами в SQL требуют точного понимания используемой СУБД. Для большинства задач вычитания дат применяются функции DATEDIFF, DATE_PART, INTERVAL, в зависимости от используемой системы – будь то MySQL, PostgreSQL или SQL Server.
В MySQL для вычисления разницы между двумя датами используется DATEDIFF(date1, date2). Результат – целое число дней между date1 и date2. Например, SELECT DATEDIFF(‘2025-05-01’, ‘2025-04-24’); вернёт 7.
В PostgreSQL разницу между датами проще всего получить через обычное вычитание: SELECT ‘2025-05-01’::date — ‘2025-04-24’::date; – результатом будет значение 7. Для большей гибкости применяется AGE(), возвращающая интервал: SELECT AGE(‘2025-05-01’, ‘2025-04-24’); – результат 7 days.
В SQL Server основным инструментом служит DATEDIFF, синтаксис: SELECT DATEDIFF(DAY, ‘2025-04-24’, ‘2025-05-01’);. Здесь важно указание единицы измерения интервала: DAY, MONTH, YEAR и т.д.
Следует учитывать, что DATEDIFF возвращает разницу между граничными значениями, не учитывая время суток, в то время как работа с TIMESTAMP в PostgreSQL позволяет получать точную разницу вплоть до секунд: SELECT ‘2025-05-01 12:00:00’::timestamp — ‘2025-04-24 08:30:00’::timestamp;.
Для фильтрации данных по интервалу между датами можно использовать выражения вида: WHERE DATEDIFF(DAY, order_date, CURRENT_DATE) > 30 в SQL Server или WHERE order_date < CURRENT_DATE — INTERVAL ’30 days’ в PostgreSQL.
Как вычислить разницу в днях между двумя датами
Для получения количества дней между двумя датами в SQL используется функция DATEDIFF (в MySQL, SQL Server) или оператор вычитания (в PostgreSQL и Oracle).
- MySQL:
SELECT DATEDIFF('2025-05-10', '2025-04-25');– вернёт15. - SQL Server:
SELECT DATEDIFF(DAY, '2025-04-25', '2025-05-10');– результат тот же:15. - PostgreSQL:
SELECT '2025-05-10'::date - '2025-04-25'::date;– вернёт15. - Oracle:
SELECT TO_DATE('2025-05-10', 'YYYY-MM-DD') - TO_DATE('2025-04-25', 'YYYY-MM-DD') FROM DUAL;– тоже15.
Если даты хранятся в таблице, допустим в столбцах start_date и end_date, применяется тот же подход:
- MySQL:
SELECT DATEDIFF(end_date, start_date) FROM your_table; - PostgreSQL:
SELECT end_date - start_date FROM your_table;
При работе с временными зонами или типами DATETIME важно приводить значения к DATE, чтобы исключить влияние времени:
SELECT DATEDIFF(DATE(end_datetime), DATE(start_datetime)) FROM your_table;– MySQLSELECT CAST(end_datetime AS DATE) - CAST(start_datetime AS DATE) FROM your_table;– PostgreSQL
Для исключения отрицательных значений можно использовать ABS:
SELECT ABS(DATEDIFF(end_date, start_date)) FROM your_table;
Получение разницы в часах, минутах и секундах
Для расчёта разницы между двумя временными метками в часах, минутах и секундах можно использовать функцию EXTRACT совместно с оператором вычитания.
Пример: пусть есть две колонки start_time и end_time в формате TIMESTAMP. Чтобы получить разницу в секундах, используется выражение:
SELECT EXTRACT(EPOCH FROM end_time - start_time) AS diff_seconds FROM table_name;Значение будет представлено в секундах с плавающей точкой. Чтобы получить отдельно часы, минуты и секунды:
SELECT
FLOOR(EXTRACT(EPOCH FROM end_time - start_time) / 3600) AS hours,
FLOOR(MOD(EXTRACT(EPOCH FROM end_time - start_time), 3600) / 60) AS minutes,
FLOOR(MOD(EXTRACT(EPOCH FROM end_time - start_time), 60)) AS seconds
FROM table_name;Если используется MySQL, применяется функция TIMESTAMPDIFF для часов и минут, а для секунд – дополнительно вычитание минут, умноженных на 60:
SELECT
TIMESTAMPDIFF(HOUR, start_time, end_time) AS hours,
TIMESTAMPDIFF(MINUTE, start_time, end_time) % 60 AS minutes,
TIMESTAMPDIFF(SECOND, start_time, end_time) % 60 AS seconds
FROM table_name;В SQL Server можно использовать DATEDIFF и DATEPART, но точность ограничена целыми единицами. Альтернатива – перевод в секунды и последующий разбор:
SELECT
DATEDIFF(SECOND, start_time, end_time) / 3600 AS hours,
(DATEDIFF(SECOND, start_time, end_time) % 3600) / 60 AS minutes,
DATEDIFF(SECOND, start_time, end_time) % 60 AS seconds
FROM table_name;Если нужна точность до долей секунды, в PostgreSQL можно использовать JUSTIFY_INTERVAL или форматировать результат AGE с дополнительной обработкой.
Разница между датами в формате INTERVAL
Тип данных INTERVAL используется для хранения разницы между двумя датами или временем. Он позволяет точно определить количество лет, месяцев, дней, часов, минут и секунд между двумя значениями.
В PostgreSQL разница между датами возвращается в формате INTERVAL по умолчанию. Пример:
SELECT AGE('2025-04-24', '2020-01-10');Результат: 5 years 3 mons 14 days. Это значение не приводится к числу – его можно использовать напрямую в арифметике с датами.
Чтобы получить интервал в другом виде, можно использовать вычитание напрямую:
SELECT '2025-04-24'::date - '2020-01-10'::date;Результат: 1931 days. Здесь возвращается также INTERVAL, но без разбиения на годы и месяцы. Для преобразования в число:
SELECT EXTRACT(DAY FROM ('2025-04-24'::date - '2020-01-10'::date));Если нужно разбить INTERVAL на составляющие, используйте EXTRACT по каждому компоненту:
SELECT
EXTRACT(YEAR FROM AGE('2025-04-24', '2020-01-10')) AS years,
EXTRACT(MONTH FROM AGE('2025-04-24', '2020-01-10')) AS months,
EXTRACT(DAY FROM AGE('2025-04-24', '2020-01-10')) AS days;INTERVAL можно складывать, вычитать и умножать на числа. Пример:
SELECT NOW() + INTERVAL '2 months 10 days';Для хранения интервалов в таблицах используется тип INTERVAL. Он поддерживает точность до микросекунд, если задана соответствующая спецификация, например INTERVAL DAY TO SECOND.
В MySQL тип INTERVAL – это ключевое слово, а не тип данных. Разница между датами в MySQL возвращается как число дней. Пример:
SELECT DATEDIFF('2025-04-24', '2020-01-10');Результат: 1931. Для расчёта времени используется TIMEDIFF, но он не подходит для работы с датами, только с временем.
В Oracle разница между датами также возвращается в днях. Для получения интервала в виде объекта применяется тип INTERVAL и функции NUMTODSINTERVAL, NUMTOYMINTERVAL.
В SQL Server используется функция DATEDIFF, которая возвращает разницу в указанной единице (день, месяц, год и др.):
SELECT DATEDIFF(DAY, '2020-01-10', '2025-04-24');Формат INTERVAL как тип данных напрямую не поддерживается в SQL Server и MySQL, но его можно эмулировать через числовые типы и функции.
Как учитывать временные зоны при вычитании дат
При вычитании дат в SQL необходимо учитывать временные зоны, чтобы избежать смещения на несколько часов, особенно при работе с глобальными системами. Большинство СУБД предоставляют типы данных, хранящие временную зону, например, TIMESTAMP WITH TIME ZONE в PostgreSQL или DATETIMEOFFSET в SQL Server.
В PostgreSQL, чтобы правильно вычесть даты с учётом временной зоны, используйте:
SELECT
(timestamp '2025-04-24 12:00:00+03' - timestamp '2025-04-24 09:00:00+00') AS diff;Результат: 00:00:00. Оба значения приводятся к единой временной зоне перед вычитанием.
Если данные хранятся без информации о временной зоне (TIMESTAMP WITHOUT TIME ZONE), рекомендуется явно указывать зону:
SELECT
(timestamp '2025-04-24 12:00:00' AT TIME ZONE 'Europe/Moscow') -
(timestamp '2025-04-24 09:00:00' AT TIME ZONE 'UTC') AS diff;В SQL Server используйте тип DATETIMEOFFSET и функцию SWITCHOFFSET:
SELECT
DATEDIFF(MINUTE,
SWITCHOFFSET('2025-04-24T09:00:00+00:00', '+03:00'),
'2025-04-24T12:00:00+03:00') AS diff_minutes;Результат: 0 минут разницы. Это позволяет корректно учитывать переходы между часовыми поясами.
Работа с типами DATE, TIME и TIMESTAMP при вычитании
Тип DATE содержит только календарную дату. При вычитании двух значений этого типа результатом будет целое число – количество дней между датами:
SELECT ‘2025-04-20’::date — ‘2025-04-10’::date;
Результат: 10
Тип TIME хранит только время суток без даты. При вычитании двух значений этого типа результат – интервал (interval), отражающий разницу во времени:
SELECT ’15:30:00′::time — ’12:00:00′::time;
Результат: 03:30:00
Если вычитание даёт отрицательное значение, оно сохраняется в виде интервала с минусом:
SELECT ’08:00:00′::time — ’12:00:00′::time;
Результат: -04:00:00
Тип TIMESTAMP содержит и дату, и время. Вычитание двух TIMESTAMP возвращает интервал с точностью до микросекунд:
SELECT ‘2025-04-20 14:00:00’::timestamp — ‘2025-04-18 10:30:00’::timestamp;
Результат: 2 days 3:30:00
При работе с DATE и TIMESTAMP важно учитывать тип результата: DATE — DATE даёт целое число, TIMESTAMP — TIMESTAMP – интервал. Для получения количества часов или минут используйте функцию EXTRACT или приведение:
SELECT EXTRACT(EPOCH FROM (‘2025-04-20 14:00:00’::timestamp — ‘2025-04-20 08:00:00’::timestamp)) / 3600;
Результат: 6
При вычитании типов разных категорий (например, DATE — TIMESTAMP) необходимо привести типы вручную, иначе возникнет ошибка или неожиданный результат.
Использование функций DATEDIFF и TIMESTAMPDIFF в разных СУБД
Функции DATEDIFF и TIMESTAMPDIFF широко используются для вычисления разницы между датами в SQL-запросах. Они могут значительно различаться в разных СУБД по синтаксису и поведению, что важно учитывать при разработке запросов. Рассмотрим, как эти функции работают в MySQL, PostgreSQL и SQL Server.
MySQL: В MySQL обе функции могут быть использованы для вычисления разницы между датами. DATEDIFF возвращает количество дней между двумя датами. Пример запроса:
SELECT DATEDIFF('2025-05-01', '2025-04-01');
Этот запрос вернёт количество дней между 1 мая и 1 апреля 2025 года. Если нужно вычислить разницу в других единицах времени, следует использовать TIMESTAMPDIFF. Например, для вычисления разницы в месяцах:
SELECT TIMESTAMPDIFF(MONTH, '2025-04-01', '2025-05-01');
Функция TIMESTAMPDIFF в MySQL позволяет указать единицу измерения (SECOND, MINUTE, HOUR, DAY, MONTH, YEAR и другие), что дает гибкость при выполнении расчетов.
PostgreSQL: В PostgreSQL функция DATEDIFF отсутствует, но аналогичное поведение можно получить с помощью простого вычитания дат, что вернёт интервал:
SELECT '2025-05-01'::date - '2025-04-01'::date;
Этот запрос также вернёт разницу в днях. Для получения разницы в других единицах времени, например, в месяцах, нужно использовать EXTRACT:
SELECT EXTRACT(MONTH FROM '2025-05-01'::timestamp - '2025-04-01'::timestamp);
В PostgreSQL нет функции TIMESTAMPDIFF, но вы можете использовать кастомные вычисления с помощью операторов для работы с датами и временем, что обеспечивает высокую гибкость.
SQL Server: В SQL Server DATEDIFF работает аналогично MySQL, но с другим синтаксисом. В SQL Server первая дата всегда указывается как второй аргумент функции:
SELECT DATEDIFF(DAY, '2025-04-01', '2025-05-01');
Этот запрос вернёт количество дней между датами. SQL Server также поддерживает функцию TIMESTAMPDIFF, но в виде аналогичной функции DATEADD, которая используется для работы с интервалами времени. Например, чтобы вычислить разницу в месяцах:
SELECT DATEDIFF(MONTH, '2025-04-01', '2025-05-01');
В отличие от MySQL, в SQL Server вы указываете единицу измерения первым аргументом, что следует учитывать при написании запросов.
Важно учитывать, что для работы с функциями DATEDIFF и TIMESTAMPDIFF в разных СУБД могут быть нюансы, связанные с точностью и типами данных. Например, при вычитании дат в PostgreSQL могут возникать различия в точности по сравнению с MySQL или SQL Server, если интервал превышает определённый диапазон.
Фильтрация записей по разнице между датами
Фильтрация данных по разнице между датами – полезный инструмент для работы с временными интервалами в SQL. Это позволяет выбрать записи, соответствующие определённым временным условиям, например, по дням, месяцам или годам. В SQL для таких задач часто используется функция DATEDIFF, которая вычисляет разницу между двумя датами в выбранных единицах (дни, месяцы, годы и т. д.).
Вот несколько примеров, как можно фильтровать записи с использованием разницы между датами:
- Фильтрация по дням: Для того чтобы выбрать записи, где разница между текущей датой и датой события меньше определённого числа дней, используется следующий запрос:
SELECT * FROM события WHERE DATEDIFF(CURDATE(), дата_события) < 30;
Этот запрос вернёт все записи, где событие произошло в последние 30 дней.
- Фильтрация по месяцам: Чтобы выбрать записи за последние 3 месяца, можно использовать функцию DATE_SUB вместе с DATEDIFF:
SELECT * FROM события WHERE дата_события > DATE_SUB(CURDATE(), INTERVAL 3 MONTH);
Здесь выбираются события, произошедшие за последние три месяца от текущей даты.
- Фильтрация по году: Для поиска событий, произошедших в текущем году, можно использовать функцию YEAR:
SELECT * FROM события WHERE YEAR(дата_события) = YEAR(CURDATE());
Этот запрос вернёт все записи за текущий год.
- Фильтрация по диапазону дат: Для выбора событий, произошедших в определённом временном промежутке, используется конструкция BETWEEN:
SELECT * FROM события WHERE дата_события BETWEEN '2025-01-01' AND '2025-12-31';
Этот запрос вернёт события, которые произошли в 2025 году.
Такой подход позволяет эффективно отфильтровывать данные по временным интервалам, что полезно для анализа событий, выполнения отчётов и многого другого.
Загрузка...
