Why is UTC datetime being converted to local datetime?

[b2wise]

Hi all,

I am using DateTimeZone.UtcNow() to generate UTC time and I am using From.Number to convert it to a number so that I can subtract my local time and find the difference.

UTC datetime is generating correctly initially but when I use From.Number power query seems to be converting it to local time without any warning.

As you can see in the image below the UTC datetime is correct but when I convert it using Number.From([UTC])
the resulting decimal number is almost exactly the same as the decimal number of my local time.

What's interesting is that UTC also converts to local time if I change the type to Datetime instead of Datetime Timezone but I'm not sure if that's supposed to happen.

Any ideas?

@AlexisOlson @BA_Pete 

Thanks in advance!

[b2wise]

@AlexisOlson Perfect! DateTimeZone.RemoveZone solved the issue. Based on what you wrote I'm understanding the UTC is calculated in Power Query from LocalNow and that's why it kept reverting back to local time.

To answer your question of the seconds not matching that is because Local Time in my report is an API call to timezonedb.com based on https://radacad.com/solving-dax-time-zone-issue-in-power-bi.

What I'm trying to do is convert UTC to local time and am using the diffrential between UTC and local time to identify daylight savings and determine how many hours to subtract.

Thanks again!

[AlexisOlson · Accepted solution]

The weird thing to me is that your seconds digits are different. I would expect them to match exactly. Maybe a caching thing? Does Refresh Preview make them match?

The reason the number for local time and UTC should match is that they both represent the same thing, just in a different format. If you remove the time zone from UTC, then they'll be different.

DateTimeZone.RemoveZone(DateTimeZone.UtcNow())

To get the offset of your local time, you can use

DateTimeZone.ZoneHours(DateTimeZone.LocalNow())

If your local time zone also has a minutes offset, then you'll want to use DateTimeZone.ZoneMinutes or, alternatively, DateTimeZone.ToRecord to get all the components.