cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jernejp
Helper IV
Helper IV

Working with UTC time and converting it

Hi,

 

I'm building an application for work travel, that is writing to a database that has the UTC timezone set, and then writes things to our ERP which is also set to UTC timezone. As per this article: https://docs.microsoft.com/sl-si/powerapps/maker/canvas-apps/functions/function-dateadd-datediff I'm trying to convert whatever the user is entering to UTC and then display it back. So when the work trip starts, I patch it to our database like so:

 

Set(
    Trip;
    Patch(
        '[dbo].[trip]';
        Defaults('[dbo].[trip]');
        {
            start: DateAdd(
                now;
                TimeZoneOffset();
                Minutes
            );
            user: User().Email;
        }
    )
);;

The problem is, that it gets patched to the DB (where the "now" variable is basically Now() that refreshes often). So the problem is that it patches local times, instead of UTC. So for example it patched 12:25 when it should have done 14:25. Any idea why the conversion doesn't work?

 

Then a few screens later, I use the patched variable

Text(DateAdd( Trip.start; -TimeZoneOffset(Trip.start); Minutes ); "[$-SL-si]dd. mm. yyyy hh:mm")

and it shows 14:25 instead of 12:25.

 

Why is this happening?

 

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
v-xida-msft
Community Support
Community Support

HI @jernejp ,

Based on the issue that you mentioned, I think you have some misunderstanding with the date/time value work mechanism in PowerApps.

 

Actually, when working date/time values within PowerApps, it would be converted into UTC Time zone value automatically. So when you put a date time value (e.g. Now()) for the start column in your Patch formula, the date/time value would be converted into a UTC time zone value firstly, then pass the converted UTC time zone value into your SQL Table.

Within your Patch formula, it is not necessary to use a DateAdd(...) formula to convert your current time (Now()) into UTC time firstly, the passed date time value would be converted into UTC time automatically, and pass it to your SQL Table.

On your side, please consider modify your formula as below:

 

Set(
    Trip;
    Patch(
        '[dbo].[trip]';
        Defaults('[dbo].[trip]');
        {
            start: Now();           /* <-- Type Now() formula here directly */
            user: User().Email;
        }
    )
)

 

 

If you want the start column of your SQL Table ('[dbo].[trip]') to store same date time value as that in your app, please modify above formula as below:

Set(
    Trip;
    Patch(
        '[dbo].[trip]';
        Defaults('[dbo].[trip]');
        {
            start: DateAdd(Now(), -TimeZoneOffset(), Minutes);           /* <-- Modify your formula here  */
            user: User().Email;
        }
    )
)

 

Please consider take a try with above solution, then check if the issue is solved.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

7 REPLIES 7
Rebetcha
MVP

Hi @jernejp ,

 

To my knowledge PowerApps allways converts everything in the background automatically to UTC. My guess is the problem starts with trying to convert it to UTC when saving it. 

Have you tried symply saving the datetime without the conversion?

Hi @Rebetcha 
I tried patching the value without conversion at 7:14 local time, and it patched 9:14 to the database. Whereas the UTC time should have been 5:14.

That's why I'm confused what's happening

So I tried displaying this:

DateAdd(Now(); TimeZoneOffset(); Minutes)

and it shows the correct UTC time. But when I use the same line of code in my Patch function, it patches in my local timezone. 

Any ideas what's going on?

v-xida-msft
Community Support
Community Support

HI @jernejp ,

Based on the issue that you mentioned, I think you have some misunderstanding with the date/time value work mechanism in PowerApps.

 

Actually, when working date/time values within PowerApps, it would be converted into UTC Time zone value automatically. So when you put a date time value (e.g. Now()) for the start column in your Patch formula, the date/time value would be converted into a UTC time zone value firstly, then pass the converted UTC time zone value into your SQL Table.

Within your Patch formula, it is not necessary to use a DateAdd(...) formula to convert your current time (Now()) into UTC time firstly, the passed date time value would be converted into UTC time automatically, and pass it to your SQL Table.

On your side, please consider modify your formula as below:

 

Set(
    Trip;
    Patch(
        '[dbo].[trip]';
        Defaults('[dbo].[trip]');
        {
            start: Now();           /* <-- Type Now() formula here directly */
            user: User().Email;
        }
    )
)

 

 

If you want the start column of your SQL Table ('[dbo].[trip]') to store same date time value as that in your app, please modify above formula as below:

Set(
    Trip;
    Patch(
        '[dbo].[trip]';
        Defaults('[dbo].[trip]');
        {
            start: DateAdd(Now(), -TimeZoneOffset(), Minutes);           /* <-- Modify your formula here  */
            user: User().Email;
        }
    )
)

 

Please consider take a try with above solution, then check if the issue is solved.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Hm, @v-xida-msft  I tried patching just with Now() to see what will happen, and the time that was added was +2 hours from our timezone, which amounts to +4 hours UTC. I don't think the conversion to UTC is happening automatically. 

Posting again to increase visibility.

Hi @jernejp , when I Patch a time value that I want to ensure is in UTC, I have used the following function and uploaded as a text value:

 

 

Text(DateAdd(Now(), TimeZoneOffset(), Minutes), "[$-en-US]yyyy-mm-ddThh:mm") & ":00.000Z";

 

 

I hope this helps you!

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Users online (2,543)