Преобразование SQL Server DateTimeOffset в часовой пояс

Возможно ли в запросе SQL Server преобразовать datetimeoffset в соответствующий часовой пояс? Например, у меня есть значения, хранящиеся как:

2015-01-03 00:30 -06:00

2015-01-10 00:30 -05:00

Я хочу, чтобы мой sql-запрос отображал -06:00 как CST или CDT и -05:00 как EST или EDT, если действует летнее время.

Я кодирую RSS-канал, где в конечном итоге хочу, чтобы мой столбец набора результатов отображался:

Пт, 02 января 2015 г. 18:30 CST

Пт, 9 января 2015 г., 18:30 по восточному поясному времени.

Любая помощь будет принята с благодарностью. Спасибо!


sql-server sql-server-2012
person behofmann    schedule 04.01.2017    source источник
comment
Почему -06:00 CST? Это может быть любой из допустимых часовых поясов. Вам нужны только часовые пояса США? Вы можете использовать DATEPART(TZoffest, YourDate) и выражение case.   -  person Sean Lange    schedule 04.01.2017
comment
SQL Server не предоставляет никаких встроенных функций, возвращающих имя часового пояса DateTimeOffset. Вам нужно либо создать такую ​​функцию, либо выполнить форматирование в приложении-потребителе.   -  person Wagner DosAnjos    schedule 04.01.2017
comment
@wdosanjos для этого не нужна функция. Если есть только часовые пояса США, простое выражение case может легко справиться с этим.   -  person Sean Lange    schedule 04.01.2017
comment
Спасибо за ваши ответы. У меня будут записи/смещения международного времени, поэтому я хочу иметь возможность отображать смещение как соответствующее имя часового пояса. Должен ли я создать таблицу часовых поясов, где смещение является ключом, извлекающим смещение, и выполнить соединение?   -  person behofmann    schedule 04.01.2017
comment
@behofmann решение более сложное, потому что вам нужно учитывать правила перехода на летнее время в разных часовых поясах.   -  person Wagner DosAnjos    schedule 04.01.2017
comment
@behofmann отмечает, что правила перехода на летнее время со временем меняются, поэтому точный алгоритм должен учитывать это, чтобы правильно обрабатывать предыдущую дату / время.   -  person Wagner DosAnjos    schedule 04.01.2017
comment
У меня есть таблица местоположений, в которой есть внешний ключ для первичного ключа в таблице часовых поясов. При этом я могу получить имя часового пояса, связанное с местоположением.   -  person behofmann    schedule 05.01.2017
comment
Мои записи хранятся в столбце datetimeoffset на основе GMT. Например, если подумать о переходе на летнее время по центральному поясному времени (DST), смещение изменится на 60 минут, НО время не изменится. Вычитание 60 минут для летнего времени из GMT дало бы мне неправильное время. Насколько я понимаю сейчас, я бы просто изменил название часового пояса с CST на CDT. Я правильно мыслю?   -  person behofmann    schedule 05.01.2017

Ответы (2)


arrow_upward
0
arrow_downward

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

create table #TimeZoneExample
(
    MyDateTimeOffset datetimeoffset
)

insert #TimeZoneExample
select '2015-01-03 00:30 -06:00' union all
select '2015-01-10 00:30 -05:00'

select convert(datetime, MyDateTimeOffset) as PostDate
    , case DATEPART(TZoffset, MyDateTimeOffset)/60 
        when -5 then 'EST'
        when -6 then 'CST'
        when -7 then 'MST'
        when -8 then 'PST'
        else 'Unknown Time Zone'
    end
from #TimeZoneExample

drop table #TimeZoneExample
person Sean Lange    schedule 04.01.2017
comment
К сожалению, это не учитывает переход на летнее время. - person Wagner DosAnjos; 04.01.2017
comment
Хороший вопрос @wdosanjos, и, конечно, было бы почти невозможно сделать это даже близко, учитывая предоставленную информацию о примере, потому что не во всех местах / странах / штатах используется летнее время. Вам нужно будет значительно расширить это, чтобы приспособить международное время, как это было предложено. И любой точный результат потребует гораздо больше информации, чем просто смещение даты и времени. Вам также нужно знать местоположение, чтобы вы могли определить точное время. Не уверен, что здесь нужно 100%, но, безусловно, стоит упомянуть. - person Sean Lange; 04.01.2017

arrow_upward
0
arrow_downward

Когда мне пришлось поработать над этим, я понял, что единственный вариант, который действительно работает, — это сохранить все дату и время в формате UTC и включить индикатор часового пояса, который привязан к таблице, показывающей, когда изменяется любой часовой пояс, например переход на летнее время, имеет место. Для этого потребуется специальный код для ваших данных.

К счастью, кто-то поддерживает базу данных часовых поясов, включая переходы на летнее время и тому подобное — БД часовых поясов IANA. Вы можете использовать и обновлять необработанные данные самостоятельно или искать другие примеры того, как это сделать, включая SQL Server. Проект поддержки часовых поясов на GitHub.

В конце концов, UTC, часовой пояс и база данных преобразования — это единственный способ обеспечить идеальную точность И конвертируемость времени между часовыми поясами, особенно те редкие TZ, которые отличаются от UTC на 15 минут или 1/2 часа.

person Laughing Vergil    schedule 04.01.2017