Преобразование даты времени из часового пояса в часовой пояс в sql-сервере

У меня есть столбец отметки времени UNIX в моей таблице базы данных, которая поступает из системы, находящейся в часовом поясе Кувейта.

Часовой пояс моего сервера базы данных – « Eastern Time US & Canada . Теперь мне нужно преобразовать отметку времени UNIX в значение даты часового пояса Kuwait, используя SQL-запрос.

Может ли кто-нибудь сказать мне, как я могу конвертировать этот штамп времени UNIX в значение даты часового пояса Кувейта?

Временные метки Unix – это целое число секунд с 1 января 1970 UTC.

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

Сначала преобразуйте временную метку в тип datetime :

 SELECT DATEADD(second, yourTimeStamp, '1970-01-01') 

Это будет дата и время UTC, соответствующее вашей отметке времени.

Затем вам нужно знать, как настроить это значение в целевом часовом поясе. В большинстве стран одна зона может иметь несколько смещений из-за летнего времени.

К сожалению, SQL Server не имеет возможности напрямую работать с часовыми поясами. Поэтому, если бы вы, например, использовали время в США по Тихоокеанскому времени, у вас не было бы возможности узнать, следует ли вычесть 7 часов или 8 часов. Другие базы данных (Oracle, Postgres, MySql и т. Д.) Имеют встроенные способы решения этой проблемы, но, увы, SQL Server этого не делает. Поэтому, если вы ищете решение общего назначения, вам нужно будет выполнить одно из следующих действий:

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

  • Используйте xp_regread чтобы получить ключи реестра Windows, содержащие данные часового пояса, и снова используйте кучу пользовательской логики для разрешения смещения для определенной даты. Конечно, xp_regread – это плохая вещь, требующая определенных разрешений и не поддерживаемая или документа.

  • Напишите функцию SQLCLR, которая использует class TimeZoneInfo в .Net. К сожалению, для этого требуется «небезопасная» assembly SQLCLR и может привести к возникновению плохих вещей.

IMHO, ни один из этих подходов не очень хорош, и нет хорошего решения для этого непосредственно в SQL. Лучшим решением было бы вернуть значение UTC (либо исходное целое число, либо время datetime в UTC) в код вашего вызывающего приложения, и вместо этого сделать там временное преобразование (с, например, TimeZoneInfo в .Net или аналогичные механизмы в других платформ).

ОДНАКО – вам повезло в том, что Кувейт (и всегда был) в зоне, которая не меняется для летнего времени. Он всегда был UTC + 03: 00. Таким образом, вы можете просто добавить три часа и вернуть результат:

 SELECT DATEADD(hour, 3, DATEADD(second, yourTimeStamp, '1970-01-01')) 

Но имейте в виду, что это не универсальное решение, которое будет работать в любом часовом поясе.

Если вы захотите, вы можете вернуть один из других типов данных SQL, например datetimeoffset , но это поможет вам понять, что это значение смещено на три часа, чтобы кто-нибудь мог его рассмотреть. Это не приведет к тому, что процесс конверсии будет отличаться или лучше.


Обновленный ответ

Я создал проект для поддержки часовых поясов в SQL Server. Вы можете установить его здесь . Затем вы можете просто преобразовать:

 SELECT Tzdb.UtcToLocal('2015-07-01 00:00:00', 'Asia/Kuwait') 

Вы можете использовать любой часовой пояс из базы данных IANA tz , включая те, которые используют летнее время.

Вы можете использовать метод, показанный выше, для преобразования из временной метки unix. Объединяя их вместе:

 SELECT Tzdb.UtcToLocal(DATEADD(second, yourTimeStamp, '1970-01-01'), 'Asia/Kuwait') 

Обновлено снова

С SQL Server 2016 теперь имеется встроенная поддержка часовых поясов с оператором AT TIME ZONE . Это также доступно в базе данных Azure SQL (v12).

 SELECT DATEADD(second, yourTimeStamp, '1970-01-01') AT TIME ZONE 'Arab Standard Time' 

Дополнительные примеры в этом объявлении.

Давайте будем гением компьютера.