Converting UTC Time to Local Time Accounting for DST

[agilio]

Hi All,

This Topic has most likely been beat to death but I wanted to share my solution with the community. We commonly see scenarios where it is needed to determine the local time from UTC factoring in DST (Daylight Savings Time). In addition we wanted that to dynamically happen each year. Below is my solution to that problem for Users of PureCloud. You will need to adjust the GMT offsets for your particular need but I hope this helps.

AddMinutes(Flow.StartDateTimeUtc,
(
If(Flow.StartDateTimeUtc >= GetDayOfWeekOccurrence(1,2,Year(Flow.StartDateTimeUtc),3,2,0,0)
and
Flow.StartDateTimeUtc <= GetDayOfWeekOccurrence(1,1,Year(Flow.StartDateTimeUtc),11,1,0,0)

,-300
,-360
)
)

)

Thanks

Anthony G
ConvergeOne

[anon28066628]

Thank you for sharing, Anthony!

A similar formula is available in the Resource Center as well.

[MelissaBailey]

Anthony we like your expression better than ours. It's been added to the the 3rd example in the resource center doc.

[Ana_Laia]

Hi everyone!

This seems to be a good solution and I tried to adapt it to Europe. In Europe the transaction dates are the last Sunday of March and October (https://en.wikipedia.org/wiki/Summer_Time_in_Europe#Exact_transition_dates). Therefore the last Sunday could be the fourth or the fifth depending of the year.

For this, I created the following variables:

Task.year = Year(GetCurrentDateTimeUtc())

Task.dayMarch = 31-(4+floor(5*task.year/4))%7 (based on the link formula)

Task.dayOctober = 31-(1+floor(5*task.year/4))%7 (based on the link formula)

With these variables the current time for Ireland/Portugal/United Kingdom:

AddMinutes(Flow.StartDateTimeUtc,
(If(Flow.StartDateTimeUtc >= MakeDateTime(Task.year,3,Task.dayMarch,1,0,0)
and Flow.StartDateTimeUtc <= MakeDateTime(Task.year,10,Task.dayOctober,1,0,0),
0,
60)
)
)

I tested it and it seems to work correctly, but could you confirm if this solution is 100% correct?

Thank you in advance.

Best regards,
Ana Laia

[agilio]

Hi Ana,

Since it is the last Sunday of the Month I believe the function(GetDayOfWeekOccurence) allows you to put a negative occurrence so you should just be able to use -1 which should give you the last occurrence of whatever day of the Month you are looking for. This might make it more straight forward. Hope this helps.

For example
AddMinutes(Flow.StartDateTimeUtc,
(
If(Flow.StartDateTimeUtc >= GetDayOfWeekOccurrence(1,-1,Year(Flow.StartDateTimeUtc),3,1,0,0)
and
Flow.StartDateTimeUtc <= GetDayOfWeekOccurrence(1,-1,Year(Flow.StartDateTimeUtc),10,1,0,0)

,0
,60
)
)

Thanks

Anthony

[Ana_Laia]

Thank you Anthony! I tried it and it worked

I noticed that I had a mistake on my expression.
For Portugal/London it should be:

AddMinutes(Flow.StartDateTimeUtc,
(
If(Flow.StartDateTimeUtc >= GetDayOfWeekOccurrence(1,-1,Year(Flow.StartDateTimeUtc),3,1,0,0)
and
Flow.StartDateTimeUtc <= GetDayOfWeekOccurrence(1,-1,Year(Flow.StartDateTimeUtc),10,1,0,0)

,60
,0
)
)

During DST, the UTC offset is +1 hours (or 60 minutes) and when not in DST there's no UTC offset (0 minutes)

[agilio]

Glad that worked sorry about the flip flop.