cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
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

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?

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
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Community Highlights

Community Highlights

Check out the Power Platform Community Highlights

Top Solution Authors
Top Kudoed Authors
Users online (7,471)