cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Hairy_Drumroll
Level: Powered On

Displaying Date Time - time always offset

I know there's a stack of date/time format questions already in the forums, but is there a better way of fixing the incorrect display of a date/time record than using this code?:

Text(DateAdd(ThisItem.CommentDate, TimeZoneOffset(), Minutes),"[$-en-US]dd mmm yyyy h:mm AM/PM")

I tried using:

Text(ThisItem.CommentDate,"dd mmm yyyy h:mm AM/PM")

But it always defaults back to:

Text(ThisItem.CommentDate,"[$-en-US]dd mmm yyyy h:mm AM/PM")

So what ends up happening is the time is always displayed incorrectly. This doesn't seem to be an issue with date only fields.

 

I can keep using the TimeZoneOffset method, but it's a bit of a kludge.

 

The data source is an in-house SQL DB.

1 ACCEPTED SOLUTION

Accepted Solutions
PowerApps Staff CarlosFigueira
PowerApps Staff

Re: Displaying Date Time - time always offset

TL;DR: if you want to use SQL date/time columns in PowerApps, please use the column type 'datetimeoffset' - that type matches with the date/time representation of PowerApps.

 

And now for the detailed answer. Depending on how the data is stored in the data source, you may need to do that offset to "fix" the time that is shown in the application. In the case of SQL databases, there are two ways to represent date/time values:

  • datetime (and datetime2 / smalldatetime, whose only difference is range/precision)
  • datetimeoffset

The datetime (and friends) column type represent an absolute value of date and an absolute value of time. That's a subtle but important difference from datetimeoffset, which represents a point in time. PowerApps date/time values represent the latter, so if you use datetime (and friends) in SQL with PowerApps, then you may have the offset issues.

 

An example may help to clarify this. If I select a date/time value in PowerApps of 2018-05-22 9:20 (morning), and I'm on the US Pacific Time Zone (UTC - 0700), it will treat the value exactly the same as if someone in London selected the value 2018-05-22 17:20 (5:20 in the afternoon). Even though they are different numeric values (9:20 vs. 17:20), they represent the same instant in time. When using PowerApps (or SQL datetimeoffset columns), you'll have the same value (*) stored in the database.

 

If you're using datetime columns, however, that concept of "point in time" doesn't exist - it's an absolute value whose location where you're looking at the value doesn't matter. A SQL datetime value of '2018-05-15 12:00:00' means "May 15th, 2018, at noon" - regardless of whether we're in the United States, Brazil, Australia, Russia or any other place. For different places the point in time will potentially be different (noon on May 15th in Australia is still May 14th on the United States). So if you're using this type of column in SQL, depending on how the data was entered in SQL, you may need to adjust it to display in PowerApps.

 

Hope this helps!

 

(*) SQL may store the two values differently, for example, as 2018-05-22 09:20:00-0700 vs. 2018-05-22 17:20:00+0100, or even 2018-05-22 16:20:00+00:00 (UTC time) or 2018-05-23 02:20:00+1000 (Sydney, Australia time), but they all still represent the same instant.

2 REPLIES 2
PowerApps Staff CarlosFigueira
PowerApps Staff

Re: Displaying Date Time - time always offset

TL;DR: if you want to use SQL date/time columns in PowerApps, please use the column type 'datetimeoffset' - that type matches with the date/time representation of PowerApps.

 

And now for the detailed answer. Depending on how the data is stored in the data source, you may need to do that offset to "fix" the time that is shown in the application. In the case of SQL databases, there are two ways to represent date/time values:

  • datetime (and datetime2 / smalldatetime, whose only difference is range/precision)
  • datetimeoffset

The datetime (and friends) column type represent an absolute value of date and an absolute value of time. That's a subtle but important difference from datetimeoffset, which represents a point in time. PowerApps date/time values represent the latter, so if you use datetime (and friends) in SQL with PowerApps, then you may have the offset issues.

 

An example may help to clarify this. If I select a date/time value in PowerApps of 2018-05-22 9:20 (morning), and I'm on the US Pacific Time Zone (UTC - 0700), it will treat the value exactly the same as if someone in London selected the value 2018-05-22 17:20 (5:20 in the afternoon). Even though they are different numeric values (9:20 vs. 17:20), they represent the same instant in time. When using PowerApps (or SQL datetimeoffset columns), you'll have the same value (*) stored in the database.

 

If you're using datetime columns, however, that concept of "point in time" doesn't exist - it's an absolute value whose location where you're looking at the value doesn't matter. A SQL datetime value of '2018-05-15 12:00:00' means "May 15th, 2018, at noon" - regardless of whether we're in the United States, Brazil, Australia, Russia or any other place. For different places the point in time will potentially be different (noon on May 15th in Australia is still May 14th on the United States). So if you're using this type of column in SQL, depending on how the data was entered in SQL, you may need to adjust it to display in PowerApps.

 

Hope this helps!

 

(*) SQL may store the two values differently, for example, as 2018-05-22 09:20:00-0700 vs. 2018-05-22 17:20:00+0100, or even 2018-05-22 16:20:00+00:00 (UTC time) or 2018-05-23 02:20:00+1000 (Sydney, Australia time), but they all still represent the same instant.

Hairy_Drumroll
Level: Powered On

Re: Displaying Date Time - time always offset

Wow! Now that's an answer!

Surprised I'm the first person to ask though.

Thanks for your help.

Helpful resources

Announcements
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

Users Online
Currently online: 83 members 4,476 guests
Please welcome our newest community members: