PowerApps sharepointlist Timezone

[ptcsoderlund]

I have set up a sharepoint list with a date column, If I add a record on the date 2017-jan-27 for example.

My powerapps app shows the date as 2017-jan-26. If I change timezone in powerapps to utc instead of local it shows the date 2017-dec-25.

What is up with that, I get it that they handle timezones differently (sharepoint vs powerapps). But this can´t be right.

What am I missing?

The sharepoint list is set to Timezone stockholm (utc + 01), Powerapps are set to local and that should also be utc + 01 on every client (so far).

[Meneghino · Accepted solution]

Hi @ptcsoderlund

I agree that the way timezones are handled by PowerApps is really confusing, but I think timezones in databases are really confusing anyway.  I have tried a couple of times to figure it out but gave up.  There is no real proper documentation on this.

What I do for now is to use an integer column instead to store my date.  I store the date as the integer of yyyymmdd, so today would be 20170123.  This has the advantages that:

• the date is the same however you access the database
• there is no hidden time portion
• it is easily parsable (i.e. converted to a date, and a date is easily converted to this integer)
• you can sort this column in date order just like a date
• the column can be filtered just like dates

If you still need to show a date column in SharePoint you can add a calculated column to your SharePoint list to be

=DATE(ROUNDDOWN(MyDateInteger/10000,0),ROUNDDOWN(MyDateInteger/100,0)-ROUNDDOWN(MyDateInteger/10000,0)*100,MyDateInteger-ROUNDDOWN(MyDateInteger/100,0)*100)

Please let me know what you think

[ptcsoderlund]

Good idea, I will probably use it if I write applications that feed data.

My end users want to have a date format so I have to leave it as date format.

I am currently using Date +1 in powerapps, which will stop working when the client localtime is not utc + 01.

I just find it weird that timezone can get date 2 days wrong from sharepoint to powerapps.

Seems to me that the only solution is to use UTC 00 on both places to get correct date.

[ptcsoderlund]

I´m not a fan of accepting workarounds as a solution. I think the problem should be fixed by the powerapps/sharepoint dev teams.

But this is the best help I/one can get.

[afernando]

I'm also having the same issue and unfortunately, the workaround mentioned here isn't going to help me because I connect to my database using directquery in Power BI. And it doesn't allow changing data types. (int to date).

This is really frustrating. 

[ptcsoderlund]

Agree, frustrating.

You can´t depend on powerapps as it is now, then you would have to design database and systems based on powerapps.

When you really should design powerapps after database and systems.

[martinav]

Nothing new on this in over a year.  I'm having this issue.  I dont want work-arounds because I also have date+time fields, and daylight savings times are an issue.  I have all of my dates stored in SP as utc() from flow.  I thought that to be the most "pure" format.  When you look at a SP list, it all looks good.  Dates convert.  When I use datacards, dates also seem to be good in PA.  However, when using labels or ThisItem.DateVariable then things get screwy.  I will probably post a new issue on this.

[ptcsoderlund]

You should, but dont expect things to change.

Power BI also suffers from this.

In PBI i am inserting data at utc, when viewing in live tiles date gets converted to localutc. But when creating reports time is in utc without any option to set local utc.

It seems to me that all the "Power" products share the same engine and problems.

I thought this would be resolved any time soon now since the services are worldwide.

I think you are right, dates should always be stored as utc and converted when viewing.

[Anonymous]

I ran into the same issue.  My workaround was to set the DateTimeZone to property to UTC and then I am manually adjusting the time myself using the DateAdd function to add 6 hours to the value.

On the DataCard I added the DateAdd to the Update property and on the date field I added it to the DefaultDate property.  Working good so far but definitely a pain to have to add it.

[ptcsoderlund]

Sweden have UTC +2 during summer and UTC +1 during winter.

I'm not ready to try and do any workarounds in order for it to work.

I´ll just stay away from powerapps for now.

Havent used powerapps in a while now, but it seems like it has not been fixed yet.

Edit: After a quick look it seems like you can choose timezone (local or UTC) now in datepicker.

Which should make everything work now. I havent tested it.