Please help with UTC

(1) Share

Report

Posted on by Meneghino

6,949

How can we set a date to be in UTC?

Text(Date(2017,1,1), UTC) gives on my system "2016-12-31T23:00:00.000Z"

This is why writing a date to a database often goes wrong by a day.

How can I obtain the value "2017-01-01T00:00:00.000Z" to be able to write to a database?

Obviously you can do it with text, but any other better way?

PS Here is how to do it with text:

DateTimeValue(Text(MyDateVariable, "yyyy-mm-dd")&"T00:00:00.000Z"))

For example this works:

Text(DateTimeValue(Text(Date(2017,1,1), "yyyy-mm-dd")&"T00:00:00.000Z"),UTC)

Categories:

Building Power Apps

I have the same question (0)

All responses (9)

Answers (1)

AndyPennell on at

Like (0)

Report

You should not need to do any conversion: just store the Date object (which internally is always in UTC) into the database. If problems persist please describe exactly what kind of database you are using, and how.

Was this reply helpful? Yes No
Meneghino 6,949 on at

Like (0)

Report

Hi @AndyPennell, thanks for following up. Your replies are always precise and concise.

I use an Access Web App as a back-end, and then create reports in Excel with Get&Transform (a.k.a. Power Query)

My users enter dates (say date of birth) in a text input since a date picker is unwieldly for disparate dates.

Since I am in Europe, the UTC internal date turns out to be a day earlier (by a few hours), and that is what is stored in the date column by a Patch operation. This is ok when displaying the data back in PowerApps in the same locale since it will make the conversion from UTC to local.

However, in Access Web Apps the date will appear as a day earlier. Also, the Excel reporting will see it as a day earlier since it does not make the conversion from UTC to local (and a very good thing that it does not, otherwise users in different locales would see different results). Also anyone opening the PowerApp in say the US will see the date of birth as a day earlier.

I have seen a few posts from European/Asian users complaining of the one day difference effect, and I suspect many US users simply have not noticed since date only local date (i.e. datetime truncated to have 00:00 time) and UTC are always on the same date.

I hope this is clear, and thanks for continuing to improve PowerApps.

Was this reply helpful? Yes No
Meneghino 6,949 on at

Like (0)

Report

PS I have just tested with CDS and the problem is the same.

How should one store birthdays so that they are shown as the same date irrespective of locale?

The only failsafe way I have thought of so far is simply to store birthdays as yyyymmdd integers, which allows for sorting and filtering for periods. It is also obviously locale independent.

Was this reply helpful? Yes No
Verified answer

AndyPennell on at

Like (3)

Report

We have recently identified an issue with Date (not DateTime) when serialized to/from some data sources. For right now, I recommend persisting dates as strings (something like "2017-02-08" for today) until we get the fix completed and rolled out.

Was this reply helpful? Yes No
sparky13 24 on at

Like (0)

Report

@AndyPennell Has this issue been resolved?

It still appears to be an issue (or I may be seeing different issues)

Was this reply helpful? Yes No
Meneghino 6,949 on at

Like (0)

Report

Hi @sparky13, what is your specific issue?

Was this reply helpful? Yes No
sparky13 24 on at

Like (0)

Report

@Meneghino. The specific issue is that comparison of dates using the date format does not work. And as a result transforming it to a text format has to be used. The limiting factor of this, is that using a date as a text format cannot be delegated.

Was this reply helpful? Yes No
Meneghino 6,949 on at

Like (0)

Report

Hi @sparky13, if you use calcualted columns then it can be delegated.
http://baizini-it.com/blog/index.php/2017/09/28/powerapps-and-azure-sql-database-current-issues/#list_1

Was this reply helpful? Yes No
MK1 169 on at

Like (0)

Report

Hi,
In my scenario.

My PowerApp Form has a date time column coming form SQL Table.
while a user submitting the form, It will take the date and time of the request created by the user.
But when users from different timezones submit their request. the date column on SQL table is populated based on their timezones.
can we set the date and time column to populate as Eastern timezone for all users on Powerapp Form.

There is an approval process once the form is submitted.
Based on this approval process the Date and time column will be updated to the time of approval. can we set this time as one timezone for all users using flow. So that the dateand time column will be updated based on one timezone.
Please let me know your thoughts.

Thanks in Advance
MK

Was this reply helpful? Yes No