У меня есть столбец отметки времени UNIX в моей таблице базы данных, которая поступает из системы, находящейся в часовом поясе Кувейта.
Часовой пояс сервера базы данных Eastern Time US & Canada. Теперь мне нужно преобразовать отметку времени UNIX в значение даты часового пояса Kuwait, используя SQL-запрос.
Может ли кто-нибудь сказать мне, как я могу преобразовать эту отметку времени UNIX в значение даты часового пояса Кувейта?
Временные метки Unix - это целое число секунд с 1 января 1970 года по UTC.
Предположим, вы имеете в виду, что у вас есть цельный столбец в базе данных с этим номером, тогда часовой пояс вашего сервера базы данных не имеет значения.
Сначала преобразуйте метку времени в тип datetime:
SELECT DATEADD(second, yourTimeStamp, '1970-01-01')
Это будет UTC datetime, который соответствует вашей отметке времени.
Затем вам нужно знать, как настроить это значение в целевом часовом поясе. В большинстве стран одна зона может иметь несколько смещений из-за летнего времени.
К сожалению, SQL Server не имеет возможности напрямую работать с часовыми поясами. Поэтому, если бы вы, например, использовали время в США по Тихоокеанскому времени, у вас не было бы возможности узнать, следует ли вычесть 7 часов или 8 часов. Другие базы данных (Oracle, Postgres, MySql и т.д.) Имеют встроенные способы решения этой проблемы, но, увы, SQL Server этого не делает. Поэтому, если вы ищете решение общего назначения, вам нужно будет сделать одно из следующих действий:
Импортируйте данные часового пояса в таблицу и поддерживайте эту таблицу по мере изменения правил часовых поясов. Используйте эту таблицу с кучей пользовательской логики для разрешения смещения для определенной даты.
Используйте xp_regread, чтобы получить ключи реестра Windows, содержащие данные часового пояса, и снова используйте кучу пользовательской логики для разрешения смещения для определенной даты. Конечно, xp_regread - это плохая вещь, требует определенных разрешений и не поддерживается или документа.
Напишите функцию SQLCLR, которая использует класс TimeZoneInfo в .Net. К сожалению, для этого требуется "небезопасная" сборка 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'