Округление datetime до минуты и часа в T-SQL: примеры функций

Быстрый ответ

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

SELECT DATEADD(MINUTE, ROUND(DATEDIFF(SECOND, '20000101', GETDATE()) / 60.0, 0), '20000101')

А для округления до ближайшего часа подходит такой код:

SELECT DATEADD(HOUR, ROUND(DATEDIFF(SECOND, '20000101', GETDATE()) / 3600.0, 0), '20000101')

В обоих случаях замените GETDATE() на конкретное значение datetime, которое вы хотите округлить. '20000101' является фиксированной точкой отсчета времени, нужной для корректного округления.

Что за функции?

Основыми инструментами в нашем случае являются функции DATEADD и DATEDIFF, использующиеся с '20000101' в качестве стартовой точки времени. Это обеспечивает точное округление времени.

Ради более точного округления времени до минуты или часа, добавляется или вычитается полминуты (30 секунд) или полчаса (1800 секунд) соответственно. Вследствие этого, функция ROUND округлит результат до ближайшего значения.

Визуализация

Попробуем визуализировать время как мишень для стрелкового выстрела, где цель – попасть как можно ближе к центру:

Центр мишени: 🎯     |    🕒 Момент выстрела: 10:29:58

Если округляем до минуты, получаем следующее: 🎯🕑

| Действие          | Время  |
| ----------------- | ------ |
| Изначальное время | 🎯🕒➡️🕒 10:29 |
| Округление        | 🎯🕒➡️🕒 10:30 |

Если же округляем до часа, выходит так: 🎯⏰

| Действие          | Время  |
| ----------------- | ------ |
| Изначальное время | 🎯🕒➡️⏰ 10:00 |
| Округление        | 🎯🕒➡️⏰ 11:00 |

Цель нашего округления – не просто достичь ближайшей минуты или часа, но и приблизиться к центру мишени.

Погружение в детали

Использование CAST и CONVERT

Округление времени до минуты также возможно с помощью функций CAST и CONVERT:

Вот пример округления до минуты:

SELECT CONVERT(DATETIME, CONVERT(VARCHAR, GETDATE(), 120))

Для вывода в формате ЧЧ:ММ:

SELECT CONVERT(CHAR(5), GETDATE(), 108)

В обоих сценариях секунды будут игнорироваться и мы получим только часы и минуты.

Подробнее об этом расскажет наш спикер на видео

Функции Azure: DATETRUNC и DATE_BUCKET

В SQL Server 2022/Azure можно использовать функции DATETRUNC и DATE_BUCKET для упрощения округления:

Округление до ближайшей минуты с DATETRUNC:

SELECT DATETRUNC('minute', GETDATE())

А для достижения ближайшей минуты с DATE_BUCKET:

SELECT DATE_BUCKET(1, MINUTE, GETDATE())

Работа со специфичными данными

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

Препятствия на пути

Учёт часовых поясов

При округлении, важно не забывать учитывать часовые пояса, чтобы избежать ошибок округления.

Точность округления

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

Совместимость версий SQL Server

При работе с разными версиями SQL Server следует учитывать их совместимость. Некоторые функции могут отсутствовать или работать по-разному в различных версиях серверов.

Полезные материалы

Эти ресурсы помогут вам лучше разобраться в теме работы с датой и временем в SQL:

1. ROUND (Transact-SQL) — SQL Server. Описание функции ROUND.
2. DATEPART (Transact-SQL) — SQL Server. Обзор функции DATEPART.
3. DATEDIFF (Transact-SQL) — SQL Server. Подробная информация по функции DATEDIFF и временным интервалам.
4. FORMAT (Transact-SQL) — SQL Server. Руководство по форматированию даты с помощью FORMAT.
5. CAST and CONVERT (Transact-SQL) — SQL Server. Руководство по преобразованию типов данных через функции CAST и CONVERT.
6. CEILING (Transact-SQL) — SQL Server. Обзор функций округления CEILING и FLOOR.