Forum Discussion
Time Zone issue
Hello,
I have a strange issue with data/time column. I have date/time column that shows correct time in Power Query but when I load data to a table I see time in another time zone (+6:00). I changed my local settings to desired time zone but I still see wrong time zone in my table.
I changed my local settings in Control Panel to be in line with time zone from Power Query.
This screenshot shows data in Power Query (this is correct and what I want to present in a table - 9:45:54AM)
When I load data to a table, this is what I get (3:45:54AM). It is obvious that +6:00 hours are added. How can i solve this?
Yes, I'm 2 hours ahead of GMT (Central European Time), andI want to present data based on EST time (NYC time). What I did so far:
My PC settings is English (United States). Power BI settings is Enhlish (US) too.
I have "Time" column that shows GMT time (see below):
Then I added Local Time column in Power Query Edutor that converts GMT from Time column to local time (-4:00). And I got what I wanted in Power Query Editor.
But, a problem arises here. When I load Local Time data to a table I see my Central European time in the table column despite my PC settings and Power BI settings are English (US).
So instead of 9:45:54AM as in Power Query Editor, I see 3:45:54PM in my table.
I am curious to know why this happen. The local time that I see in Power Query Editor should be loaded to my table if the settings is correct.
In the meantime I solved this by adding another column to a table with a formula, but it would be great if I can do this without additional column.
LocalTime 2 = MyTable[LocalTime] - .25 ---> 0.25 = 6/24 (I am 6 hours ahead of EST).
------
How I got the local time:
=DateTimeZone.ToLocal([start_time])
Where [start-time] column shows time from my server (GMT) that is converted to local time (EST).
4 Replies
What function are you using to get the local date and time?
https://msdn.microsoft.com/en-us/query-bi/m/datetimezone-functions
When you publish the report the local function will actually give you the server local time which most likely won't be your time?
Try something like this...
DateTimeZone.SwitchZone(DateTimeZone.UtcNow(), -5 ))
I'm in the Central US Time Zone which right now because of DST is 5 hours behind ( instead of the usual 6 hours )
You can get your value here
Hope this helps! :smileyhappy:
What timezone is your PC set to? What part of the world are you in?
Yes, I'm 2 hours ahead of GMT (Central European Time), andI want to present data based on EST time (NYC time). What I did so far:
My PC settings is English (United States). Power BI settings is Enhlish (US) too.
I have "Time" column that shows GMT time (see below):
Then I added Local Time column in Power Query Edutor that converts GMT from Time column to local time (-4:00). And I got what I wanted in Power Query Editor.
But, a problem arises here. When I load Local Time data to a table I see my Central European time in the table column despite my PC settings and Power BI settings are English (US).
So instead of 9:45:54AM as in Power Query Editor, I see 3:45:54PM in my table.
I am curious to know why this happen. The local time that I see in Power Query Editor should be loaded to my table if the settings is correct.
In the meantime I solved this by adding another column to a table with a formula, but it would be great if I can do this without additional column.
LocalTime 2 = MyTable[LocalTime] - .25 ---> 0.25 = 6/24 (I am 6 hours ahead of EST).
------
How I got the local time:
=DateTimeZone.ToLocal([start_time])
Where [start-time] column shows time from my server (GMT) that is converted to local time (EST).
