Answered

Time after Time

(1) Share

Report

Posted on by Community Power Pla... Microsoft Employee

"...If you fall I will catch you, I will be waiting... time after time..." (Sorry, had to!)
Anyone see what i'm missing here?

Need to calculate the diff between varNowUTC and Timestamp + 24HRS UTC

Timestamp UTC: Self-explanatory

Timestamp + 24HRS UTC:

Text(
 DateAdd(
 DateTimeValue(ThisItem.timestamp), 24,Hours), "[$-en-US]yyyy-mm-dd hh:mm:ss")

varNowUTC: (bound to a 1 sec timer that keeps a "clock" of sorts running)

Text(varNow, DateTimeFormat.UTC)

Time Remaining:

Text(
 Time(
 0,
 0,
 DateDiff(
 DateTimeValue(varNOWUTC.Text),
 DateTimeValue('Timestamp+24HRSUTC'.Text),
 Seconds
 )
 ),
 "[$-en-US]hh:mm:ss"
)

It appears that somewhere, I'm losing UTC. Does the Time() function convert to Local?

Categories:

Building Power Apps

I have the same question (0)

All responses (9)

Answers (1)

Sort by

CarlosFigueira Microsoft Employee on at

Like (2)

Report
Copy link

Link copied!

Time after time (sorry, couldn't resist either), when you do a conversion between a Date/Time value and text (string), you may end up mixing up the time zones where the dates are stored. PowerApps always stores internally dates in the UTC time zone, but it by default always displays / parses dates in the local time zone.

When a string is converted to a Date/Time value (via the DateTimeValue function), it will try to find from the string which timezone it should be converted. If it has an indication (such as the 'Z' suffix - that indicates UTC - that you have in your varNowUTC variable), then it will use that time zone. If it doesn't see any indication (like what you have in the 'Timestamp UTC' variable and the 'Timestamp+24HRSUTC' label, then it will assume that the value represents a local time. Since you have one string that represents a local time and one that represents a UTC time, you'll see this difference.

The best practice when working with date/time values is to only do the conversion at the point when you want to either display the value to the user, or parse the value from a user input (or a data source), to minimize the number of places where this can go wrong. Or if you really need to have intermediate steps where you want to do conversions, then you must be careful not to strip off the time zone indicator.

In your case, if ThisItem.timestamp has a timestamp in UTC (with the appropriate suffix - 'Z' or '+00:00'), then it will be interpreted as such. But when you converted it back to a text in the Timestamp+24HRSUTC label, you didn't add any suffixes, so if you parse that value again with the DateTimeValue function, it will assume that it is a local time.

Hope this helps!

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!
Thanks for the response @CarlosFigueira . Love the Cyndi Lauper reference!!
In debugging further:
Wrapping `ThisItem.timestamp` with `Text(ThisItem.timestamp, DateTimeFormat.UTC)` did not translate it to a (ISO8601?) UTC timestamp string.
It actually stays the same.
Before: `2019-03-25 14:53:21.0`
After: `2019-03-25 14:53:21.0`
Stripping UTC off of everything...
Timestamp: `ThisItem.timestamp`
Timestamp + 24HRS:
Text( DateAdd( DateTimeValue(ThisItem.timestamp), 24,Hours ), "[$-en-US]yyyy-mm-dd hh:mm:ss" )
varNow: `Text(varNow, "yyyy-mm-dd hh:mm:ss")` (Instantiated from a 1 sec timer with: Set(varNow, Now()) )
Time Remaining:
Text( Time( 0, 0, DateDiff( DateTimeValue(varNOW.Text), DateTimeValue('timestamp+24HRS'.Text), Seconds ) ), "[$-en-US]hh:mm:ss" )
Same Result: Time Remaining in this case should read 23:58:59
Not sure where I'm dropping 17 hours!

Are your keen eyes seeing what I'm missing?

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!
The trick here is:
Data Source (ThisItem.timestamp) is presented in UTC, but not in ISO8601 format
TimeStamp+24HRS is an in-app translation (at the whims of PowerApps "internal" stuff)
varNow is being captured (via Now() ) in local date-time
TimeRemaining needs to be the diff between TimeStamp+24HRS and varNow in hh:mm:ss format.
One nice note: TimeRemaining will ALWAYS be < 24 hrs so at least we don't have to deal with "leftovers" in the form of dd 🙂 .
Should I try instantiating varNow in UTC?

Was this reply helpful? Yes No
Verified answer

CarlosFigueira Microsoft Employee on at

Like (3)

Report
Copy link

Link copied!
@ericonline wrote:

Wrapping `ThisItem.timestamp` with `Text(ThisItem.timestamp, DateTimeFormat.UTC)` did not translate it to a (ISO8601?) UTC timestamp string.

It actually stays the same.

Before: `2019-03-25 14:53:21.0`

After: `2019-03-25 14:53:21.0`

This probably means that the 'timestamp' column is of type string, so the Text function won't change it - if the first parameter is a string Text just returns it "as is". To get the value in the ISO8601 format, you can first parse it as a Date/Time value - but you need to tell PowerApps that the value is from UTC - as since it doesn't specify anything it will be treated as local. You can add the 'Z' suffix to that:

Text(DateTimeValue(ThisItem.timestamp & "Z"), DateTimeFormat.UTC)

Now that value represents a UTC date time and it also tells PowerApps that this is the case. So if you want to calculate differences between this time and other times, you can now do that.

The attached app shows how you could implement this logic. To open it, save it locally, then go to https://create.powerapps.com, select Open -> Browse, and find the file that you saved.

Hope this helps! And remember that if you're lost you can look and you will find help here, time after time 🙂

ForumThread257182...

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (1)

Report
Copy link

Link copied!
Your sample app was a tremendous help @CarlosFigueira. But i'm curious...if I fall will you catch me? Will you be waiting? HAHAH~!!
Progeny, here is what I landed on:
Convert Timestamp from Data Source, from a string to Local Time:
This is a user-facing label
Text( DateAdd( DateTimeValue(ThisItem.timestamp), - //<--THIS PART WAS NOT INTUITIVE TimeZoneOffset( DateTimeValue(ThisItem.timestamp) ), Minutes ), "[$-en-US]yyyy-mm-dd hh:mm:ss" )
Convert Timestamp from Data Source, from a string to a PowerApps-parsable UTC timestamp:
Hidden from user, only for comparison
Text( DateTimeValue(ThisItem.timestamp & "Z"), //<--WHO KNEW!! Adding "Z" and PowerApps calls it "UTC"!! DateTimeFormat.UTC )
Convert Timestamp from Data Source, from a string to a PowerApps-parsable UTC timestamp and add 24hrs.
Hidden from user, only for comparison
Text( DateAdd( DateTimeValue(ThisItem.timestamp & "Z"), 24, Hours ), DateTimeFormat.UTC )
Establish a rolling Now()
Hidden from user, only for comparison
Again, varNow is instantiated OnTimerEnd of a 1sec Timer Control
Text(varNow, DateTimeZone.UTC)
Compare varNow and Timestamp from Data Source + 24hrs
This is a user-facing label
Text( Time( 0, 0, DateDiff( DateTimeValue(varNOWUTC.Text), DateTimeValue('Timestamp+24HRSUTC'.Text), Seconds ) ), "[$-en-US]hh:mm:ss" )
Visual aid:

I'm due to produce a dissertation on PowerApps time sometime soon. It gets even crazier (can you believe it!) when you create a SQL database with the various data types. HAHA!~

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!
@CarlosFigueira, one last question:
When using...
DateAdd( DateTimeValue(ThisItem.timestamp), - //<--THIS PART WAS NOT INTUITIVE TimeZoneOffset( DateTimeValue(ThisItem.timestamp) )
...to convert UTC to Local Time, where is the TimeZoneOffset derived from?
I ask because I've noticed that PowerApps seems to pull Lat/Long from network config somehow.
Example, when...
- Using PowerApp on laptop (no device sensors)
AND
- Connected to my org's VPN
...my Lat/Long shows the lat long of the VPN server!!
I reeeaaalllyyy hope TimeZoneOffset doesn't pull my locale from my network config but rather my OS locale.

Was this reply helpful? Yes No
CarlosFigueira Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!
@ericonline wrote:

Convert Timestamp from Data Source, from a string to Local Time:

This is a user-facing label

Text( DateAdd( DateTimeValue(ThisItem.timestamp), - //<--THIS PART WAS NOT INTUITIVE TimeZoneOffset( DateTimeValue(ThisItem.timestamp) ), Minutes ), "[$-en-US]yyyy-mm-dd hh:mm:ss" )

You can simplify this expression as follows:

Text( DateTimeValue(ThisItem.timestamp & "Z"), "[$-en-US]yyyy-mm-dd hh:mm:ss" )

Working with date/time values, with different time zones, has always been a source of issues. You can use the TimeZoneOffset to convert between UTC and local time, but if you know that the original date/time value is represented as a text, and it represents a UTC time, and it doesn't have any suffix to denote the time zone, then this is a simpler expression.

Was this reply helpful? Yes No
CarlosFigueira Microsoft Employee on at

Like (2)

Report
Copy link

Link copied!

TimeZoneOffset gets the client's time zone from the the client's OS, not from the VPN. The operation happens entirely on the client (browser) side, so you can feel safe with using it.

Was this reply helpful? Yes No
seadude 1,855 on at

Like (0)

Report
Copy link

Link copied!

Hi @CarlosFigueira . I've not found this to be the case.
- PowerApps seems to get my IP from somewhere OTHER than my OS Locale. My Lat/Long are different depending on my Internet connection. Tests to prove.
- Follow up of tests.

Was this reply helpful? Yes No