Преобразование UNIX timestamp в DateTime в SQL Server

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

Преобразовать UNIX-временную метку, или bigint, в datetime в SQL Server можно следующим образом:

SELECT DATEADD(SECOND, unix_временная_метка, '19700101') AS datetime_в_UTC;

Этот запрос вернет значение datetime во временной зоне UTC. 🚀

Арифметическое переполнение: когда UNIX-временная метка слишком большая

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

DECLARE @long_timestamp BIGINT = 2147483648; -- Знакомство с 2038 годом!
DECLARE @seconds_in_leap_year INT = 31622400; -- Число секунд в високосном году.
SELECT 
    DATEADD(SECOND, @long_timestamp % @seconds_in_leap_year, DATEADD(YEAR, @long_timestamp / @seconds_in_leap_year, '19700101')) AS datetime_после_преобразования;

UTC vs Местное время: время всегда относительно!

Для учета локального смещения можно воспользоваться следующим кодом:

DECLARE @Local_Time_Offset BIGINT = DATEDIFF(SECOND, GETUTCDATE(), GETDATE());
DECLARE @Unix_Timestamp BIGINT = 1620000000;
SELECT DATEADD(SECOND, (@Unix_Timestamp + @Local_Time_Offset), '19700101') AS local_date_time;

Попробуйте DateTime, привязанный к местному времени! ☕

Функция преобразования: пользуйтесь заново!

Ниже приведена функция, которая облегчит преобразование, избавив вас от необходимости помнить все детали:

CREATE FUNCTION dbo.ConvertUnixTimestampToDateTime (@UnixTimestamp BIGINT)
RETURNS DATETIME
AS
BEGIN
    DECLARE @secondsInYear INT = 31622400; -- Количество секунд в високосном году.
    RETURN DATEADD(SECOND, @UnixTimestamp % @secondsInYear, DATEADD(YEAR, @UnixTimestamp / @secondsInYear, '19700101'))
END

С помощью этой функции UNIX-временную метку можно легко конвертировать в читаемый формат DateTime:

SELECT dbo.ConvertUnixTimestampToDateTime(unix_временная_метка) AS читаемый_datetime;

Советы и уловки от опытных разработчиков

• Всегда руководствуйтесь UTC, чтобы избежать расхождений по часовым поясам.
• Проверьте, чтобы строка '19700101' была в формате YYYYMMDD.
• Тщательно проверяйте результаты, особенно при работе с будущими датами, чтобы избежать переполнения.
• При обработке дат после 2038 года, незаменимой станет модульная арифметика.

DateTime в UNIX-временную метку: возвращение назад

Чтобы преобразовать DateTime обратно в UNIX-временную метку, воспользуйтесь этим кодом:

SELECT DATEDIFF(SECOND, '19700101', поле_datetime) AS конвертированная_unix_временная_метка
FROM ваша_таблица;

Учтите формат входных данных DateTime, чтобы избежать ошибок преобразования.

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

Визуализация процесса преобразования UNIX-временной метки в datetime:

📅 UNIX-временная метка (начало отсчета -- 1970-01-01): 1620000000  
⏳ ...время идет... ⏳  
🗓️ Современная календарная дата: 2021-05-03 09:33:20

Процесс преобразования, в свою очередь, можно сравнить с путешествием во времени:

- UNIX-временная метка — это счетчик времени, исчисленный в секундах с начала эпохи Unix.
- Преобразуем его в текущую дату и время.

Передвиньтесь во времени:

SELECT DATEADD(SECOND, unix_timestamp_field, '19700101') AS datetime
FROM ваша_таблица;

⌛ Добро пожаловать в будущее c датами, понятными каждому 📆!

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

1. CAST и CONVERT (Transact-SQL) — официальная документация по функциям CAST и CONVERT.
2. DATEADD (Transact-SQL) — подробная информация о функции DATEADD.
3. Преобразование Unix-временной метки в дату в SQL Server — руководство по конвертации временных меток.
4. Эффективное взаимодействие для программистов — полезный доклад о коммуникации между разработчиками, хоть он и не относится к SQL.