cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Creea1
Frequent Visitor

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. 

1 ACCEPTED SOLUTION
Creea1
Frequent Visitor

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

YearStartEndOffset
20212021-09-26T02:002022-04-03T03:0013
20212021-04-03T03:002021-09-26T02:0012

 

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. 

 

 

 

 

 

 

 

View solution in original post

5 REPLIES 5
V-pazhen-msft
Community Support
Community Support

@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 

watkinnc
Super User
Super User

If the offset of the DateTimeZone is a concern, you could use each DateTimeZone.ToUtc([ColumnName]) so that your value is always UTC.

 

--Nate


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!
Creea1
Frequent Visitor

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

YearStartEndOffset
20212021-09-26T02:002022-04-03T03:0013
20212021-04-03T03:002021-09-26T02:0012

 

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. 

 

 

 

 

 

 

 

View solution in original post

Greg_Deckler
Super User
Super User

@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.


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Kudoed Authors