Forum Discussion
TimeZone problems
Hi RvdHeijden,
In order to verify whether the timezone of the source and destination matches you can do the following:
In Query Editor, change the type of column to "Date/Time/Timezone".
This will let you decide whether to use any formulas to make it align with the source.
Thanks !!
Most probably the times are converted from UTC to local (or vice versa), somewhere in Power Query (M).
If you provide some fake example data and the relevant part of the Power Query code, then we will be able to judge what needs to be changed.
ive check the data in the Query Editor and all dates where set tot +00:00, where it should read +02:00 right ?
You can change the Time to 'Local Time' but then it adds 2 hours (which is good) but sometimes it adds 1 hour, why is that ?
It all depends on what is happning to your data, e.g.:
- Changing the data type of a column from datetimezone to datetime, will convert the datetimezone to local datetime
- So does function DateTime.From
- Functions DateTime.Date and DateTime.Time will just take the date and time from the datetimezone data, without conversion to local date/time.
- Loading datetimezone data into the data model, will just cut off the zone informtion, so no conversion to local.
The reason that the difference is sometimes 1 hour and sometimes 2 hours is simple: Daylight Saving Time.
In The Netherlands, the difference is 2 hours in Summer and 1 hour in Winter.So you shouldn't try to subtract 1 or 2 hours from your datetimes, but make sure there won't be a conversion from UTC to local time as outlined above.
This will be my last reaction for now, as I'm heading for a dentist appointment to get rid of 2 wisdom teeth,
So just changing the datatype wont help in this case ?
I changed the datatype and it looks to do the trick.
Good luck at the dentist, let's hope they wont remove to much 'wisdom' :)
