cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Super User
Super User

Time after Time

"...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

image.png

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?

1 ACCEPTED SOLUTION

Accepted Solutions
PowerApps Staff CarlosFigueira
PowerApps Staff

Re: Time after Time


@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 Smiley Happy

9 REPLIES 9
PowerApps Staff CarlosFigueira
PowerApps Staff

Re: Time after Time

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!

Super User
Super User

Re: Time after Time

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! 
    • image.png

Are your keen eyes seeing what I'm missing?

Super User
Super User

Re: Time after Time

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 Smiley Happy .

Should I try instantiating varNow in UTC?

PowerApps Staff CarlosFigueira
PowerApps Staff

Re: Time after Time


@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 Smiley Happy

Super User
Super User

Re: Time after Time

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:
  • image.png
    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!~ 
Highlighted
Super User
Super User

Re: Time after Time

@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.

PowerApps Staff CarlosFigueira
PowerApps Staff

Re: Time after Time


@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.

PowerApps Staff CarlosFigueira
PowerApps Staff

Re: Time after Time

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.

seadude
Level 10

Re: Time after Time

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.

 

Helpful resources

Announcements
firstImage

PowerApps Monthly Community Call!

Join us next Wednesday for our Demo Extravaganza, October 16, 2019 8am PDT.

firstImage

Microsoft Business Applications Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

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: 534 members 5,340 guests
Please welcome our newest community members: