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

[Anonymous]

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:

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.

[gpoggi · Accepted solution]

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

[Tonny021]

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

[Syndicate_Admin]

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]

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

[Syndicate_Admin]

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

[Anonymous]

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

[Syndicate_Admin]

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]

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]

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)

[User5231]

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]

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

[nehajadhav166]

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

[Anonymous]

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.

[Anonymous]

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?

[dax]

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 · Accepted solution]

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

[vivekravi]

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!

[DannyMiletic]

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

[nehajadhav166]

@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

[tmarton]

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.

[nehajadhav166]

@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