Answered

Passing JSON to PowerAutomate

(0) Share

Report

Posted on by DeeCiii15

In my app, I have a gallery which displays a collection that contains datetime values. I pass this collection to a JSON variable and eventually on to SQL Server. These datetimes are displayed to the user in his local timezone, but converted to UTC when stored as a JSON. My issue is that some of the values are being correctly converted to UTC and some are being converted to UTC - 1 hour. I cant figure out why this is happening. You can see an example below. The first screenshot is my collection showing the correct datetimes in local format. The second screenshot is the output after I have passed the collection to a JSON. You can see that the highlighted values are correct in UTC format. All of the other values are converted to UTC - 1 hour.

(I am Eastern time for reference so it should be + 5 hours for UTC conversion)

Collection view of the date time fields in local time:

Values in my JSON variable (Supposed to be UTC):

Categories:

Building Power Apps

I have the same question (0)

All responses (4)

Answers (1)

Sort by

Verified answer

EddieE 4,641 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

@DeeCiii15

What I've come across in the past is that when there's a 1hr (+/-) difference in dates/times it's typically related to a Daylight Saving event. Does such an event occur around those dates for where you are located?

Was this reply helpful? Yes No
DeeCiii15 84 on at

Like
a
(0)

Report
Copy link

Link copied!

Indeed seems it will be March 5th. So the dates that are not effected are before that. I was thinking that since the dates are being generated today it would factor in the difference between local time and UTC on the day the dates were generated. It seems as you suggested that it is calculating the local time and UTC difference with daylight savings factored in. That is unfortunate.

Was this reply helpful? Yes No
EddieE 4,641 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!
@DeeCiii15

Yes, we have a date in October each year that does the same thing.

If it's the first Sunday in March each year you could use this code to set a variable to check dates against then adjust your collection first before sending to JSON:

// Get the current year Set(currentYear, Year(Today())); // Calculate the date of the first Sunday in March Set( firstSundayInMarch, DateValue("3/1/" & currentYear) + Mod(8 - Weekday(DateValue("3/1/" & currentYear)), 7) );

I did a couple of year tests and it seems to work ok. You may want to do the same for your own sanity?

Was this reply helpful? Yes No
DeeCiii15 84 on at

Like
a
(0)

Report
Copy link

Link copied!

Thanks for the code. I will give it a try. My issue is that my app is used in several European countries as well and the time change occurs on different dates in each lol. So I will have to account for each country.

Was this reply helpful? Yes No