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

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Power Apps June 2021

June Power Apps Community Call

Did you miss the call? Check out the recording here!

Top Solution Authors
Top Kudoed Authors
Users online (15,589)