DATETIMEOFFSET

DATETIMEOFFSET — это тип данных в реляционных системах управления базами данных (СУБД), предназначенный для хранения момента времени с учётом смещения относительно всемирного координированного времени (UTC). В отличие от типа DATETIME, который хранит только дату и время без привязки к часовому поясу, DATETIMEOFFSET позволяет однозначно определить точку на временной шкале, что критически важно для приложений, работающих с данными из разных часовых поясов.

История и стандартизация

Тип DATETIMEOFFSET был впервые представлен корпорацией Microsoft в версии SQL Server 2008. Его появление было вызвано необходимостью решения проблемы неоднозначности временных меток, возникающей при хранении данных в системах, распределённых географически. До этого разработчикам приходилось хранить время в UTC и отдельно фиксировать смещение вручную, что было чревато ошибками.

Позднее аналогичные типы данных были реализованы в других СУБД. В стандарте SQL:2011 был введён тип TIMESTAMP WITH TIME ZONE, который функционально эквивалентен DATETIMEOFFSET. В PostgreSQL, например, существует тип TIMESTAMPTZ, а в MySQL — TIMESTAMP, который, однако, хранит значения только в UTC и не сохраняет исходное смещение.

Формат хранения и представления

Внутреннее представление

В SQL Server значение DATETIMEOFFSET занимает 10 байт. Из них:

• 5 байт отводится под дату и время с точностью до 100 наносекунд (аналогично типу DATETIME2);
• 2 байта — под смещение от UTC в минутах (диапазон от -14:00 до +14:00);
• 1 байт — под точность (масштаб) дробной части секунд.

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

Стандартный формат строкового литерала для DATETIMEOFFSET в SQL Server выглядит как:

YYYY-MM-DD HH:mm:ss[.fffffff] {+|-}HH:mm

Пример: 2025-04-08 14:30:00.0000000 +03:00

Здесь +03:00 означает, что местное время опережает UTC на 3 часа (например, московское время в летний период). Если смещение отрицательное, ставится знак минус.

Ключевые характеристики

Однозначность и конвертация

Главное преимущество DATETIMEOFFSET — возможность однозначного преобразования в UTC. Система всегда знает, насколько местное время отличается от UTC, поэтому может корректно выполнять операции сравнения, сортировки и вычисления разницы между значениями, записанными в разных часовых поясах.

Например, значение 2025-04-08 12:00:00 +00:00 и 2025-04-08 15:00:00 +03:00 представляют один и тот же момент времени — 12:00 UTC. При сортировке по DATETIMEOFFSET SQL Server автоматически приведёт все значения к UTC и отсортирует их корректно.

Диапазон и точность

• Диапазон дат: от 0001-01-01 до 9999-12-31.
• Диапазон времени: от 00:00:00 до 23:59:59.9999999.
• Точность: до 100 наносекунд (7 знаков после запятой в секундах).
• Диапазон смещения: от -14:00 до +14:00 с шагом в 1 минуту.

Отличия от других типов времени

DATETIMEOFFSET vs DATETIME

• DATETIME не хранит информацию о часовом поясе. Если приложение получает время из разных регионов, без дополнительной логики невозможно определить, к какому UTC относится запись.
• DATETIME имеет меньший диапазон (1753–9999) и точность до 3.33 миллисекунды.
• DATETIME занимает 8 байт, что меньше, чем 10 байт у DATETIMEOFFSET.

DATETIMEOFFSET vs DATETIME2

• DATETIME2 имеет ту же точность и диапазон, что и DATETIMEOFFSET, но не хранит смещение.
• DATETIME2 занимает от 6 до 8 байт в зависимости от точности.
• Выбор между DATETIME2 и DATETIMEOFFSET зависит от того, нужно ли приложению сохранять информацию об исходном часовом поясе.

DATETIMEOFFSET vs TIMESTAMP (в MySQL)

• В MySQL тип TIMESTAMP хранит время в UTC и автоматически конвертирует его в часовой пояс сессии при чтении. Однако исходное смещение не сохраняется, и при изменении часового пояса сервера все существующие значения могут интерпретироваться неверно.
• DATETIMEOFFSET в SQL Server сохраняет смещение явно, что исключает подобные проблемы.

Применение

Географически распределённые системы

DATETIMEOFFSET широко используется в приложениях, где данные поступают из разных часовых поясов. Например:

• Логи событий в распределённых системах.
• Системы бронирования авиабилетов и отелей, где время отправления/заезда указывается в местном времени.
• Финансовые системы, фиксирующие время транзакций в разных регионах.

Аналитика и отчётность

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

Миграция данных

При переносе данных из старых систем, где время хранилось в DATETIME без указания часового пояса, переход на DATETIMEOFFSET требует дополнительной работы. Необходимо определить, в каком часовом поясе были записаны исторические данные, и добавить соответствующее смещение.

Особенности работы в SQL Server

Функции для работы с DATETIMEOFFSET

• SYSDATETIMEOFFSET() — возвращает текущую дату и время сервера вместе со смещением от UTC.
• TODATETIMEOFFSET(expression, time_zone) — преобразует значение DATETIME2 или DATETIME в DATETIMEOFFSET, добавляя указанное смещение.
• SWITCHOFFSET(expression, time_zone) — изменяет смещение у существующего значения DATETIMEOFFSET, не меняя сам момент времени (то есть фактически конвертирует время из одного часового пояса в другой).
• AT TIME ZONE — функция, доступная начиная с SQL Server 2016, позволяет конвертировать время между часовыми поясами, используя информацию из системного реестра Windows.

Индексация и производительность

Столбцы типа DATETIMEOFFSET могут быть проиндексированы. При сортировке SQL Server использует внутреннее представление, приведённое к UTC, поэтому индексы работают корректно независимо от исходного смещения. Однако хранение дополнительных 2 байт на смещение может незначительно увеличить размер индекса по сравнению с DATETIME2.

Критика и ограничения

• Избыточность для локальных приложений. Если все данные относятся к одному часовому поясу, использование DATETIMEOFFSET излишне и увеличивает размер базы данных.
• Сложность при работе с летним временем. DATETIMEOFFSET хранит только фиксированное смещение на момент записи. Если часовой пояс переходит на летнее время, старое смещение остаётся неизменным. Для корректной обработки переходов необходимо использовать дополнительные таблицы календарей или специализированные функции, такие как AT TIME ZONE.
• Отсутствие в некоторых СУБД. Не все базы данных поддерживают этот тип нативно. В MySQL, например, нет прямого аналога, и разработчикам приходится эмулировать его с помощью двух столбцов.

Примеры использования

Создание таблицы

``sql CREATE TABLE Events ( EventId INT PRIMARY KEY, EventName NVARCHAR(100), EventTime DATETIMEOFFSET NOT NULL ); ``

Вставка данных

``sql INSERT INTO Events (EventId, EventName, EventTime) VALUES (1, 'Конференция', '2025-04-10 10:00:00 +03:00'); ``

Запрос с конвертацией

``sql SELECT EventName, EventTime AT TIME ZONE 'UTC' AS EventTimeUTC FROM Events; ``

Источники

• Microsoft Docs: datetimeoffset (Transact-SQL)
• ISO/IEC 9075:2011, Information technology — Database languages — SQL
• PostgreSQL Documentation: Date/Time Types
• MySQL 8.0 Reference Manual: Date and Time Data Types