Power Query: операции с датой и часовым поясом

Я создаю отчет Power BI на основе данных из таблицы SQL Server User, которая содержит столбец CreatedDate типа DateTypeOffset (7). Я ищу Power Query, эквивалентный .NET [TimeZoneInfo.FindSystemTimeZoneById]. Данные моего отчета содержат информацию о часовом поясе, которая хранится как стандартный идентификатор (например, Стандартное тихоокеанское время), и мне нужно преобразовать значения в моем столбце DateTimeOffset в часовой пояс, о котором я говорил выше, вот как Я делаю это на C #:

TimeSpan timeZoneOffsetSpan = TimeZoneInfo.FindSystemTimeZoneById(settings.TimeZ​one).GetUtcOffset(UserCreatedDateTime);

Это дает мне разницу между временем в часовом поясе, хранящемся в моих настройках, и временем UTC для UserCreatedDateTime. И я выполняю эту строку кода только один раз для всех строк в моей таблице User, потому что цель приведенного выше кода - узнать текущее смещение с учетом таких функций часового пояса, как DayLightSaving.

И теперь я могу просто добавить это смещение (которое может быть положительным или отрицательным) для каждого значения в моем столбце User.CreateDateTime, в C # я делаю это с помощью [DateTimeOffset.ToOffset]:

DateTimeOffset convertedOffset = UserCreatedDateTime.ToOffset(timeZoneOffsetSpan)

Поэтому мне нужно иметь возможность выполнять те же преобразования в моем отчете Power BI, но я не смог найти ни одной функции Power Query, которая могла бы делать то же самое, что и [TimeZoneInfo.FindSystemTimeZoneById], если бы эта функция существовала, она охватила бы мою первую строку кода C # а затем мне нужно будет найти эквивалент [TimeZoneInfo.FindSystemTimeZoneById]. [DateTimeZone.SwitchZone], вероятно, то, что мне нужно, но сначала мне нужно знать смещение, соответствующее моему часовому поясу, а затем я смогу передать это смещение в качестве 2-го и 3-го параметров для этой функции.

Итак, в завершение мне нужен аналог Power BI для [TimeZoneInfo.FindSystemTimeZoneById]. Кто-нибудь может помочь?


powerbi powerquery
person AverageAsker    schedule 10.03.2016    source источник
comment
К сожалению, в Power BI нет формата datetime format, который форматирует datetimeoffset в местном времени. По состоянию на март 2019 года datetimeoffsets просто отображаются в Power BI со скрытой частью смещения.   -  person Nick.McDermaid    schedule 27.03.2019

Ответы (2)


arrow_upward
0
arrow_downward

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

См. Здесь Как можно Я выполняю эквивалент AddHours для DateTime в Power Query?

Или вы можете использовать функции DateTimeZone, встроенные в M.

person teylyn    schedule 11.03.2016
comment
Если бы у меня была эта домашняя таблица со смещением зоны, тогда мой отчет был бы неверным 2 раза в год (и я не могу себе этого позволить), то есть из-за экономии дневного света, поэтому не следует использовать жестко запрограммированное смещение - это ошибочный подход потому что это смещение будет неправильным как минимум 2 раза в год - когда мы входим в летнее время и когда выходим из него. Часовой пояс нельзя рассчитать, зная только смещение, но смещение можно рассчитать, если известен часовой пояс (подробности здесь stackoverflow.com/tags/timezone / info). Что касается функций DateTimeZone - как я показал в своем исходном вопросе, они не помогают. - person AverageAsker; 14.03.2016

arrow_upward
0
arrow_downward

Вы можете воспользоваться функцией AT TIME ZONE TSQL, чтобы создать таблицу смещений (в минутах) на каждый день для каждого часового пояса и импортировать полученную таблицу в PowerBI, чтобы вы могли добавлять / вычитать соответствующие смещения к базовому времени.

Если бы я был в ситуации OP, я бы все время импортировал (конвертировал) в UTC, а затем добавлял бы смещение в PowerBI, в зависимости от часового пояса, который вы хотите отобразить. (возможно, используя этот метод?)

В приведенном ниже примере представлена ​​таблица смещения часовых поясов Австралии относительно UTC.

Если моя целевая таблица находится в формате UTC, и я хочу изменить время в PowerBI, мне просто нужно добавить конкретное смещение к моему времени ...

+------------+-----------+----------+----------+----------+ 
| TargetDate | QLDOffset | NTOffset | SAOffset | WAOffset |
+------------+-----------+----------+----------+----------+ 
| 2018-04-02 |      -600 |     -570 |     -570 |     -480 | 
| 2018-04-01 |      -600 |     -570 |     -570 |     -480 | 
| 2018-03-31 |      -600 |     -570 |     -630 |     -480 | 
| 2018-03-30 |      -600 |     -570 |     -630 |     -480 |
+------------+-----------+----------+----------+----------+

Вы можете увидеть изменение смещения для SA 1 апреля.

WITH MostDays AS ( --MostDays is a table of 10 years worth of dates going back from today.  
               SELECT Today = CAST(DATEADD(DAY, -days, GETUTCDATE())  AS DATETIMEOFFSET)
               FROM (SELECT TOP 3650 Days = ROW_NUMBER() OVER (ORDER BY message_id )  -- this will give a rolling window of 10 years
                     FROM sys.messages
                    )x
            )
--  This section takes the datetime from above, and calculates how many minutes difference there is between each timezone for each date.
Select  TargetDate=CAST(Today AS DATE)
    ,QLDOffset = DATEDIFF(minute,CAST(Today at TIME ZONE 'UTC' AT TIME ZONE  'E. Australia Standard TIME' AS DateTime),CAST(Today at TIME ZONE 'UTC' AS DATETIME))
    ,NTOffset = DATEDIFF(minute,CAST(Today at TIME ZONE 'UTC' AT TIME ZONE  'AUS Central Standard Time' AS DateTime),CAST(Today at TIME ZONE 'UTC' AS DATETIME))
    ,SAOffset = DATEDIFF(minute,CAST(Today at TIME ZONE 'UTC' AT TIME ZONE  'Cen. Australia Standard TIME' AS DateTime),CAST(Today at TIME ZONE 'UTC' AS DATETIME))
    ,WAOffset = DATEDIFF(minute,CAST(Today at TIME ZONE 'UTC' AT TIME ZONE  'W. Australia Standard TIME' AS DateTime),CAST(Today at TIME ZONE 'UTC' AS DATETIME))
FROM MostDays

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

person Trubs    schedule 17.05.2018