Forum Discussion
DateTimeZone.switchZone using zone ID not time offset
Currently DateTimeZone.switchZone only support directly specifying a time offset not a time zone ID. I need the ID because I am in a time critical 24 hour business that cannot make time zone inaccuracies during the change over between day light saving zones.
So my problem is I want to get my NZT date time to a UTC datetimeZone so I can calculate duration difference from DateTimeZone.UtcNow()
NZT is utc +12 untill last Sunday in September at 02:00 when it becomes +13 for the period of daylight saving.
I cannot just use DateTimeZone.ToUtc([ColumnName]) because I only have a DateTime with no Zone.
I first need convert the NZT datetime to UTC by subtracting the offset duration.
[DateTimeColumnName]- #duration(0,12,0,0)
Then DateTime.AddZone([dateTime], 0) to make it as UTC DateTimeZone
Now I can calculate the duration difference with DateTimeZone.UtcNow()
When I need to report the actual date I use DateTimeZone.Switch([UTCDateTimeZone, 12) to get it to NZT format.
The problem is that the time Offset changes from +12 to +13 on September 26 2021 at 02:00
I want to be able to do the following DateTimeZone.Switch([UTCDateTimeZone, NZT) rather than DateTimeZone.Switch([UTCDateTimeZone,13)
My work around is to create a table with Daylight Saving Periods as below
Year Start End Offset 2021 2021-09-26T02:00 2022-04-03T03:00 13 2021 2021-04-03T03:00 2021-09-26T02:00 12 I then look up the offset using the dateTime I have and use the Offset Value in DateTimeZone.Switch([UTCDateTimeZone, [OffSet])
But this is a time consuming workaround it woult be much easier to just use the timezone id in switch eg DateTimeZone.Switch([UTCDateTimeZone, NZT). I have logged this as an Idea.
5 Replies
Creea1 Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.So my problem is I want to get my NZT date time to a UTC datetimeZone so I can calculate duration difference from DateTimeZone.UtcNow()
NZT is utc +12 untill last Sunday in September at 02:00 when it becomes +13 for the period of daylight saving.
I cannot just use DateTimeZone.ToUtc([ColumnName]) because I only have a DateTime with no Zone.
I first need convert the NZT datetime to UTC by subtracting the offset duration.
[DateTimeColumnName]- #duration(0,12,0,0)
Then DateTime.AddZone([dateTime], 0) to make it as UTC DateTimeZone
Now I can calculate the duration difference with DateTimeZone.UtcNow()
When I need to report the actual date I use DateTimeZone.Switch([UTCDateTimeZone, 12) to get it to NZT format.
The problem is that the time Offset changes from +12 to +13 on September 26 2021 at 02:00
I want to be able to do the following DateTimeZone.Switch([UTCDateTimeZone, NZT) rather than DateTimeZone.Switch([UTCDateTimeZone,13)
My work around is to create a table with Daylight Saving Periods as below
Year Start End Offset 2021 2021-09-26T02:00 2022-04-03T03:00 13 2021 2021-04-03T03:00 2021-09-26T02:00 12 I then look up the offset using the dateTime I have and use the Offset Value in DateTimeZone.Switch([UTCDateTimeZone, [OffSet])
But this is a time consuming workaround it woult be much easier to just use the timezone id in switch eg DateTimeZone.Switch([UTCDateTimeZone, NZT). I have logged this as an Idea.
Creea1
Thank for sharing. Can you mark your workaround as the solution, so others can find it quickly.Paul Zheng _ Community Support Team
Is there no real solution
