Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Convert Date/Time in UTC to Local Time with Daylight savings

Hello,

 

I'm not sure where is the better place to do this manipulation, but I have a column with the date/time as following:image.png

The time is in UTC and I want to convert this time to Mountain Daylight Time:

-between second Sunday of March and first Sunday of November you go back 6 hrs so UTC - 6

-between first Sunday of November and second Sunday of March you go back 7 hrs so UTC - 7

 

I found this article online that shows how to account for daylight savings on the refresh date: https://powerpivotpro.com/2019/01/dst-refresh-date-function-power-bi-service/

But I need to transform a column of data to the appropiate time. Is this possible? If it is, is it better to do in Power Query or Desktop of Power BI? Any help would be much appreciated.

 

Thank you.

 

2 ACCEPTED SOLUTIONS
gpoggi
Responsive Resident
Responsive Resident

Hi @Anonymous ,

 

I think there are many ways, for example I tried to find a pattern in order to catch the November first Sunday or March second Sunday, and for your specific needs, maybe this custom function could work:

 

(datetimecolumn as datetime) =>

let

date = DateTime.Date(datetimecolumn),
time = DateTime.Time(datetimecolumn),
firstSundayOfNovember = Date.StartOfWeek(#date(Date.Year(date), 11, 7), Day.Sunday),
SecondSundayOfMarch = Date.StartOfWeek(#date(Date.Year(date), 3, 14), Day.Sunday),

isSummerTime =	(date = SecondSundayOfMarch and time >= #time(1,0,0))
	        or
		(date > SecondSundayOfMarch and date < firstSundayOfNovember) 
		or 
		(date = firstSundayOfNovember and time >= #time(1,0,0)),


timeZone = (7 - Number.From(isSummerTime))*-1,

MDT = 
            DateTime.From(date) 
            + #duration(0,Time.Hour(time),Time.Minute(time),Time.Second(time))  
            + #duration(0, timeZone, 0, 0)

in
    MDT


So for dates from March Second Sunday at 1:00am until November First Sunday at 12:59:59am you will get your datetime - 6 hours and for dates from November First Sunday 1:00am until March Second Sunday at 12:59:59am you will get your datetime - 7 hours

According to Saint Google, the time is changed after 1:00am if you need it to be changed after 12:00am instead just remove first and last condition from isSummerTime

If you have any question or if you find any error on the code, just let me know.

 

 

Regards,

 

Gian Carlo Poggi

 

 

View solution in original post

gpoggi
Responsive Resident
Responsive Resident

Sure @Anonymous ,

 

Right click on Queries pane and add a new Blank Query:

 

PQ_1.jpg

 

Then right click on this new query and select Advanced Editor:

 

 

PQ_2.jpg

 

In this new window erase all, paste the my code and click DONE:

 

 

PQ_3.jpg

 

Now that query was converted into a function, you can rename it if you like, for example to "UTC_to_MDT":

 

 

PQ_4.jpg

 

Then in order to use this function in your table you have different options, one option is going to your query or table, then click on Add Column / Invoke Custom Function, then put a name to this new column, select your function (in my case UTC_to_MDT) and select the column from your table you need to apply this function to (in my case "Date"):

 

 

PQ_6.jpg

 

And then you will see the new date added :

 

 

PQ_7.jpg

 

Hope this helps.

 

Regards,

 

Gian Carlo Poggi

View solution in original post

61 REPLIES 61
Tonny021
New Member

My problem is fixed by making sure you are on correct time & time zone.

Syndicate_Admin
Administrator
Administrator

Thanks for sharing this solution, it's pretty disappointing that PowerQuery doesn't have proper time zone support.

I've tweaked it for conversion from UTC to UK time (aka Europe/London in tzdata)

 

From When do the clocks change? - GOV.UK (www.gov.uk):


In the UK the clocks go forward 1 hour at 1am on the last Sunday in March, and back 1 hour at 2am on the last Sunday in October.

The period when the clocks are 1 hour ahead is called British Summer Time (BST). There’s more daylight in the evenings and less in the mornings (sometimes called Daylight Saving Time).

When the clocks go back, the UK is on Greenwich Mean Time (GMT).


datetime version:

 

(datetimecolumn as datetime) =>

let

date = DateTime.Date(datetimecolumn),
time = DateTime.Time(datetimecolumn),

// From https://www.gov.uk/when-do-the-clocks-change
// In the UK the clocks go forward 1 hour at 1am on the last Sunday in March,
// and back 1 hour at 2am on the last Sunday in October.

// Last Sunday in March
ForwardDate = Date.StartOfWeek(#date(Date.Year(date), 3, 31), Day.Sunday),
// Last Sunday in October
BackDate = Date.StartOfWeek(#date(Date.Year(date), 10, 31), Day.Sunday),

isSummerTime =
		(date = ForwardDate and time >= #time(1,0,0))
	    or
		(date > ForwardDate and date < BackDate) 
		or 
		(date = BackDate and time < #time(1,0,0)),


timeZone = Number.From(isSummerTime),

Europe_London = datetimecolumn + #duration(0, timeZone, 0, 0)

in
    Europe_London

 

 

datetimezone version:

 

let
    Source = (datetimecolumn as datetimezone) =>

let

// This version ignores the zone information in the input, but adds it to the output

date = DateTime.Date(datetimecolumn),
time = DateTime.Time(datetimecolumn),

// From https://www.gov.uk/when-do-the-clocks-change
// In the UK the clocks go forward 1 hour at 1am on the last Sunday in March,
// and back 1 hour at 2am on the last Sunday in October.

// Last Sunday in March
ForwardDate = Date.StartOfWeek(#date(Date.Year(date), 3, 31), Day.Sunday),
// Last Sunday in October
BackDate = Date.StartOfWeek(#date(Date.Year(date), 10, 31), Day.Sunday),

isSummerTime =
		(date = ForwardDate and time >= #time(1,0,0))
	    or
		(date > ForwardDate and date < BackDate) 
		or 
		(date = BackDate and time < #time(1,0,0)),

timeZone = Number.From(isSummerTime),

Europe_London = DateTime.AddZone(DateTimeZone.RemoveZone(datetimecolumn) + #duration(0, timeZone, 0, 0) , timeZone)

in
    Europe_London
in
    Source

 

 

I hope that's useful to someone!

Anonymous
Not applicable

Hi Syndicate_Admin,

You write "...and back 1 hour at 2am on the last Sunday in October", then there should be time < #time(2,0,0)) instead of time < #time(1,0,0)).

(I had a similar error, I fixed it in my "UTC to CET" post.)

No, because I'm converting from UTC to UK time. The "2 am" is in British Summer time, which is 01:00 UTC.

Anonymous
Not applicable

The #time(2,0,0) function is time zone independent. When the current time (UTC, CET...) reaches 2 am, it switches to winter time.

When the clocks go back, each minute looks like this in UTC and local time:

  • 00:58 UTC / 01:58 BST
  • 00:59 UTC / 01:59 BST
  • 01:00 UTC / 01:00 GMT
  • 01:01 UTC / 01:01 GMT

The government says "Clocks go back at 2am" because your clock is in BST right up until that moment, at which point it should become GMT.

 

The source data is in UTC, so you need to figure out what the switch time is in UTC, which will probably not be the same as the switch time in local time (might even be on a different day for places like Australia!)

 

Anonymous
Not applicable

I already got the hang of it, it's been a long time since I did it. I tried entering times into a function in PowerBI and that worked, but our application sends times to PowerBI in UTC, so you're right it is necessary to find out what the switch time is in UTC versus local time. 👍 Thank you.

User5231
Helper II
Helper II

I could't find a simple Dax for this so I came up with a pretty simple solution. This is UTS to Central with DLS.

 

Adjusted Time UTC to Central =
var _Year = year('Table'[DateTime])
var _time = TIME(6,0,0)

Var SundayNovemeber = CALCULATE(min('Table'[DateTime]), month('Table'[DateTime]) = 11, WEEKDAY('Table'[DateTime],1) = 1, YEAR('Table'[DateTime]) = _Year)
Var SundayMarch = CALCULATE(min('Table'[DateTime]), month('Table'[DateTime]) = 3, WEEKDAY('Table'[DateTime],1) = 1, YEAR('Table'[DateTime]) = _Year)

Var AdjustHours = if('Table'[DateTime]<=SundayMarch&& timevalue(format('Table'[DateTime], "hh:mm:ss")) < time(2,0,0) || 'Table'[DateTime] >= SundayNovemeber && timevalue(format('Table'[DateTime], "hh:mm:ss")) < time(2,0,0), 6, 5)

Return
'Table'[DateTime] - time(AdjustHours,0,0)

Var SundayMarch should actual be...

 

Var SundayMarch = maxx(topn(2, Filter(DateTable, month(DateTable[Date]) = 3 && WEEKDAY(DateTable[Date],1) = 1 && YEAR(DateTable[Date]) = _Year), DateTable[Date] , ASC), [Date])
 
I forgot March is the 2nd Sunday not the 1st
andresballen
New Member

Hey! I created this DAX formula to convert from UTC to PDT. Considering the following rules:

 

Daylight Saving: Pacific Daylight Time (PDT) is a daylight saving/summer timezone, however during winter some places switch clocks for one hour back and observe Pacific Standard Time (PST).

 

 

Start: Pacific Daylight Time (PDT) started on Sunday, March 14, 2021 at 2:00 am local time and clocks were set one hour forward to Sunday, March 14, 2021, 3:00 am. Daylight saving starts annually the on second Sunday of March

 

 

End: Pacific Daylight Time (PDT) ends on Sunday, November 7, 2021 at 2:00 am local time and clocks are set one hour back to Sunday, November 7, 2021, 1:00 am local standard time instead. Daylight saving ends annually the on first Sunday of November

 

UTC to PDT = 
VAR CurrentDate = DATE(YEAR('Date'[Date]),MONTH('Date'[Date]),DAY('Date'[Date]))
var CurrentTime = TIME(HOUR('Date'[Date]),MINUTE('Date'[Date]),SECOND('Date'[Date]))
var March = DATE(2022,3,1)
var November = DATE(2022,11,1)
VAR SecondSundayMarch = FILTER(
        ALL('Date'[Date]),
        YEAR('Date'[Date]) = YEAR(CurrentDate) && 
        MONTH('Date'[Date]) = MONTH(March) && 
        DAY([Date]) > 7 && 
        DAY([Date]) < 15 && 
        WEEKDAY([Date],1) = 1)
VAR FirstSundayNov = FILTER(
        ALL('Date'[Date]),
        YEAR('Date'[Date]) = YEAR(CurrentDate) && 
        MONTH('Date'[Date]) = MONTH(November) && 
        DAY([Date]) >= 1 && 
        DAY([Date]) < 8 && 
        WEEKDAY([Date],1) = 1)
VAR IsSummerTime = OR(AND(CurrentDate = SecondSundayMarch, CurrentTime >= time(9,0,0)),  OR(AND(CurrentDate > SecondSundayMarch, CurrentDate < FirstSundayNov), AND(CurrentDate = FirstSundayNov, CurrentTime <= time(8,59,0))))
VAR TimeDiff = TIME(8-IsSummerTime,0,0) // If is summer time diff = 7 else time diff = 8
RETURN CurrentDate-TimeDiff

 

@aeriksen Can you please explain in steps more how do you used this formula?

I have table with UTC timestamp. Is this formula needs to be created as measure in same table?

 

Thanks,

Neha

Hi @nehajadhav166 

I've only used this in M in Power BI Services. 

1. Create a custom function

(datetimecolumn as datetime) =>
    let
    date = DateTime.Date(datetimecolumn),
    time = DateTime.Time(datetimecolumn),
    lastSundayOfOctober = Date.StartOfWeek(#date(Date.Year(date), 10, 31), Day.Sunday),
    lastSundayOfMarch = Date.StartOfWeek(#date(Date.Year(date), 3, 31), Day.Sunday),
    isSummerTime =	(date = lastSundayOfMarch and time >= #time(2,0,0))
    or
    (date > lastSundayOfMarch and date < lastSundayOfOctober)
    or
    (date = lastSundayOfOctober and time <= #time(2,0,0)),
    timeZone = 1 + Number.From(isSummerTime),
    CET =
    DateTime.From(date)
    + #duration(0,Time.Hour(time),Time.Minute(time),Time.Second(time))
    + #duration(0, timeZone, 0, 0)
in
    CET

2. Invoke custom function on the date column of your table to create a new column with the timestamp convertion

 

I'm not able to provide you with a more detailed description at this point, and my screenshots are in Norwegian, but I hope this at least will help you a bit along the way.

 

Skjermbilde 2022-12-05 kl. 10.51.41.png

Skjermbilde 2022-12-05 kl. 10.51.58.png

 

Anonymous
Not applicable

Hello, great solution!  However, I was getting "We couldn't authenticate with the credentails provided.  Please try again."  error after I refreshed the query.  My datasource is a SharePoint List and it showed that I was signed in but the credentails couldn't be autenticated.  The error went away after I removed the invoked function columns.  Any idea how to fix this?

Tristan202109_0-1632417864604.png

Tristan202109_1-1632418173274.png

 

dax
Community Support
Community Support

Hi bogdans,

I will test this for you and inform you as soon as I get it . And you also could refer to this link for details.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

gpoggi
Responsive Resident
Responsive Resident

Hi @Anonymous ,

 

I think there are many ways, for example I tried to find a pattern in order to catch the November first Sunday or March second Sunday, and for your specific needs, maybe this custom function could work:

 

(datetimecolumn as datetime) =>

let

date = DateTime.Date(datetimecolumn),
time = DateTime.Time(datetimecolumn),
firstSundayOfNovember = Date.StartOfWeek(#date(Date.Year(date), 11, 7), Day.Sunday),
SecondSundayOfMarch = Date.StartOfWeek(#date(Date.Year(date), 3, 14), Day.Sunday),

isSummerTime =	(date = SecondSundayOfMarch and time >= #time(1,0,0))
	        or
		(date > SecondSundayOfMarch and date < firstSundayOfNovember) 
		or 
		(date = firstSundayOfNovember and time >= #time(1,0,0)),


timeZone = (7 - Number.From(isSummerTime))*-1,

MDT = 
            DateTime.From(date) 
            + #duration(0,Time.Hour(time),Time.Minute(time),Time.Second(time))  
            + #duration(0, timeZone, 0, 0)

in
    MDT


So for dates from March Second Sunday at 1:00am until November First Sunday at 12:59:59am you will get your datetime - 6 hours and for dates from November First Sunday 1:00am until March Second Sunday at 12:59:59am you will get your datetime - 7 hours

According to Saint Google, the time is changed after 1:00am if you need it to be changed after 12:00am instead just remove first and last condition from isSummerTime

If you have any question or if you find any error on the code, just let me know.

 

 

Regards,

 

Gian Carlo Poggi

 

 

Hi @gpoggi 

Could you pls explain the logic here? I just used 5 instead of 7 for EST but trying to understand how this works.

Appreciate your help!

Tried this but date column looks like below with "-5:00" and getting error in the new column - thoughts on what might be wrong?

 

DannyMiletic_0-1690810649835.png

 

@gpoggi 

When I try to use above code in my table I get error:

Expression.Error: The DateTime.Date function expects an input of type DateTime or DateTimeZone.
Details:
[Table]

 

Column is of datetimezone. I also tried with datetime type but error is same for both data type.

Can you please guide me where I am going wrong?

 

Thanks,

Neha

You need your column to be formatted to Date-time. If you try to use date-time-timezone it will fail. When you invoke the function the datetimecolumn in the bottom field must be formatted to datetime only.

@tmarton ,

 

datetime column is of datatype datetime but still have same error.

What could be area of issue?

here is my m query:

let
Source = Table.SelectColumns(ProductData,{"Assembly_TimeStamp_USPacific","Assembly_Message_Payload_SourceTimestamp_USPacific"}),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Assembly_Message_Payload_SourceTimestamp_USPacific", type datetime}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "AssemblyTimeStamp", each if [Assembly_TimeStamp_USPacific] = null then [Assembly_Message_Payload_SourceTimestamp_USPacific] else [Assembly_TimeStamp_USPacific]),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"AssemblyTimeStamp", type date}, {"Assembly_TimeStamp_USPacific", type date}, {"Assembly_Message_Payload_SourceTimestamp_USPacific", type date}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type1", "Custom", each HourMinTable1),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom1", "Custom", {"Time"}, {"Custom.Time"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom.Time", type time}}),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type2", {{"AssemblyTimeStamp", type text}, {"Custom.Time", type text}}, "en-US"),{"AssemblyTimeStamp", "Custom.Time"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"DateTime"),
#"Changed Type3" = Table.TransformColumnTypes(#"Merged Columns",{{"DateTime", type datetime}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type3",{{"DateTime", "Assembly_DateTime"}}),
date = DateTime.Date("Assembly_DateTime")
in
date

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors