cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
jernejp
Level: Powered On

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 Team
Community Support Team

Re: Working with UTC time and converting it

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.
6 REPLIES 6
Rebetcha
Level 10

Re: Working with UTC time and converting it

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?

jernejp
Level: Powered On

Re: Working with UTC time and converting it

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

jernejp
Level: Powered On

Re: Working with UTC time and converting it

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 Team
Community Support Team

Re: Working with UTC time and converting it

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.
jernejp
Level: Powered On

Re: Working with UTC time and converting it

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. 

jernejp
Level: Powered On

Re: Working with UTC time and converting it

Posting again to increase visibility.

Helpful resources

Announcements
firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

SecondImage

PowerApps Monthly Community Call

Tomorrow, September 18th at 8am PDT

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

SecondImage

Power Platform Summit North America

Register by September 5 to save $200

Top Kudoed Authors
Users Online
Currently online: 211 members 4,867 guests
Recent signins:
Please welcome our newest community members: