From the course: Power BI Data Methods

Working with different time zones - Power BI Tutorial

From the course: Power BI Data Methods

Start my 1-month free trial Buy for my team

Working with different time zones

“

- [Instructor] We're all spread out all across the world in many time zones depending on where we live. This means it might be 5 p.m. where you are right now and someone else might live 12 hours ahead. To standardize dates and times across the world, we can normalize the date times to UTC or what's formally known as coordinated universal time. We can create date time values with the function date time. We see its first three inputs are year, month, and day, like the date function. After that, we input the hour, minute, and second. Notice that it doesn't have a time zone in this function. We can add a time zone using another function, hashtag date time zone, where the last two parameters represent the hours relative to UTC and the minutes. Let's use negative eight for the Pacific time. Next, let's create a date time zone list for these date time zones using the list function that supports this functionality. We'll start it on March 9th, 2024 at midnight Pacific time, which is negative eight hours behind UTC. We'll extend this out three days, which is three times 24 hours. And lastly, for our duration, we're going to input zero days, one hour and one hour between each one of these date time zones. However, as we go down the list, we might notice there's actually a problem with the way that we've set up this function, and it doesn't have anything to do with power query. The time zone changes from Pacific Standard Time on March 9th where it's eight hours behind and on 1 a.m.that day it goes forward an hour, so it's now seven hours behind UTC. Our date time zone function doesn't account for this. It uses the same time zone across all these hourly measurements. Instead, I've brought the date times in using an R script that we explored earlier that accounts for the change in time zone using the Lubridate R package. I started it on March 9th at 12 a.m. UTC, and we scroll down and we see the date times for both UTC and Pacific, so we can see the time zones changed in our table. Let's add another column for the time zone difference. We'll take the Pacific field and we'll subtract UTC from it. We'll then convert this into a duration data type. Notice it now displays negative eight on the first day when we have these measurements. However, as we scroll down, we see when the time zone changes from being negative eight hours behind UTC to negative seven. Once we have this conversion stored in a date timetable like this, we can use it in modeling without having to worry about dealing with time zone changes, because we'll just convert everything to UTC in our data and then when we want to view it in local Pacific time, we can change that where we need to. Having a date timetable like this is a game changer.

Contents