Forum Discussion
Converting Time Zones
Hello everyone,
I have a table like the below:
What I need to do is to add column that keeps the same Due Time if Timing is Cairo and change the Due Time if the the Timing is Edinburgh to convert it to Cairo time, bearing in mind the DST that is applied in UK but not in Egypt (not anymore).
Due TimeTiming
| 6:00:00 PM | Cairo |
| 5:00:00 PM | Edinburgh |
TimeZone conversions can be tricky, especially when DST applies. Unfortunately, there is not a good way to directly convert a DateTime to another zone (factoring in DST). However, there are free Rest APIs you can use that don't require an API key (and more free ones that do). Here are two examples.
If you only need to convert a time on today's date, you can simply get the offset value with this expression. You can remove the [utc_offset] part to see what other info is there. You can then use another function to add hours or switchzone by that number of hours (after you convert it to a number).
= Json.Document(Web.Contents("http://worldtimeapi.org/api/timezone/Africa/Cairo"))[utc_offset]
If you have a column of past/future datetimes and need to convert them (accounting for DST), you can use this function I wrote that uses a different REST API.
//fnConvertTimeZone
(dt as text, fromZone as text, toZone as text) =>
let
BaseURL = "https://www.timeapi.io/api/Conversion/ConvertTimeZone",
headers = [#"Content-Type" = "application/json"],
Body = [dateTime = dt, fromTimeZone = fromZone, toTimeZone = toZone, dstAmbiguity=""],
Response = Web.Contents(BaseURL, [Headers = headers, Content = Json.FromValue(Body)]),
Result = Json.Document(Response)[conversionResult][dateTime]
in
ResultYou can then Invoke Custom Column passing in your DateTime and zone info like this. I named the function fnConvertTimeZone. There is likely a limit on this API, so it may not work with too many rows.
= fnConvertTimeZone("2021-03-14 17:45:00", "Europe/Dublin", "Africa/Cairo")
or with a column reference (with the text formatted as shown above)
= fnConvertTimeZone([DateTime Column], "Europe/Dublin", "Africa/Cairo")
Pat
3 Replies
TimeZone conversions can be tricky, especially when DST applies. Unfortunately, there is not a good way to directly convert a DateTime to another zone (factoring in DST). However, there are free Rest APIs you can use that don't require an API key (and more free ones that do). Here are two examples.
If you only need to convert a time on today's date, you can simply get the offset value with this expression. You can remove the [utc_offset] part to see what other info is there. You can then use another function to add hours or switchzone by that number of hours (after you convert it to a number).
= Json.Document(Web.Contents("http://worldtimeapi.org/api/timezone/Africa/Cairo"))[utc_offset]
If you have a column of past/future datetimes and need to convert them (accounting for DST), you can use this function I wrote that uses a different REST API.
//fnConvertTimeZone
(dt as text, fromZone as text, toZone as text) =>
let
BaseURL = "https://www.timeapi.io/api/Conversion/ConvertTimeZone",
headers = [#"Content-Type" = "application/json"],
Body = [dateTime = dt, fromTimeZone = fromZone, toTimeZone = toZone, dstAmbiguity=""],
Response = Web.Contents(BaseURL, [Headers = headers, Content = Json.FromValue(Body)]),
Result = Json.Document(Response)[conversionResult][dateTime]
in
ResultYou can then Invoke Custom Column passing in your DateTime and zone info like this. I named the function fnConvertTimeZone. There is likely a limit on this API, so it may not work with too many rows.
= fnConvertTimeZone("2021-03-14 17:45:00", "Europe/Dublin", "Africa/Cairo")
or with a column reference (with the text formatted as shown above)
= fnConvertTimeZone([DateTime Column], "Europe/Dublin", "Africa/Cairo")
Pat