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

"Time" to Party? "Time" and "Time" again!

Hello @CarlosFigueira ,

Curious if you know how to solve this one. 

I have read your excellent blog post on Working with Date Times in PowerApps.  In my case, I want to store in Azure SQL, ISO8601 format of yyyy-mm-ddThh:mm:ss+/-hh:mm.

Example:

- I'm standing in Seattle, WA which is UTC-07:00 this time of year.

- Local time is 2019-06-01T18:41:00

- I want the time from PowerApps to store in SQL as 2019-06-02T01:41:00-07:00

Rationale:

- It is an ISO standard

- It holds two valuable pieces of information; UTC time and the users local time

- No casting/converting/etc. in SQL

- No confusion of what time an event occurred

- Single column rather than multiple columns required for timezone, dst offset, etc.

- I can use DATEADD statements in SQL Views

Problem:

- I can't seem to get SQL to accept the format from PowerApps

Tried so far:

- PowerApps

DateTimeValue(
    Text(
        Text(
            Now(),
            DateTimeFormat.UTC
         ), 
     "[$-en-US]yyyy-mm-ddThh:mm:ss"
      ) 
    & "-0" 
& TimeZoneOffset() / 60
& ":00" )

- SQL data type: nvarchar(25)

- Results: Its a string! Can't do data operations in SQL without CASTing/CONVERTing/etc.

- PowerApps

DateAdd(
    DateTimeValue(
        Text(
            Now(), 
            DateTimeZone.UTC
        )
    ), 
    TimeZoneOffset(
        DateTimeValue(
            Text(
                Now(), 
                DateTimeZone.UTC
             )
         )
    ), 
    Minutes
)

- SQL data type: datetime2(7)

- Results: Plain UTC string, no timezone offset on the end of the timestamp (+/-hh:mm)

- PowerApps:

DateTimeValue(
    Text(
        Now(),
        "[$-en-US]yyyy-mm-ddThh:mm:ss"
    ) 
    & "-0" 
    & TimeZoneOffset() / 60 
    & ":00"
)

- SQL data type: datetimeoffset(7)

- Results: The entire timestamp is there, but ends with +00:00, no -07:00 as expected.

image.png

Any ideas are appreciated. THank yoU!

1 ACCEPTED SOLUTION

Accepted Solutions
PowerApps Staff CarlosFigueira
PowerApps Staff

Re: "Time" to Party? "Time" and "Time" again!

As of today, PowerApps always writes date/time values to SQL using the UTC (+00:00) time zone, regardless of the timezone where the data came from (please create a new feature request in the PowerApps Ideas board to have it preserve the local time zone on those). This is documented in the Data Types page, under the Date/Sql Server section.

If you really want your local time zones preserved, you can use the first approach that you used, saving the date/time value as a string (nvarchar(25)). And to overcome the problem of not being able to do date/time operations without conversions, you can add a new calculated column to your table that does the conversion automatically, so you won't need to do the conversion in every operation.

For example, if this is your table:

CREATE TABLE PowerAppsTest.deleteme20190603 (
	Id INT IDENTITY PRIMARY KEY,
	[Name] NVARCHAR(MAX),
	[DateTimeStr] NVARCHAR(25)
)

You can add a new calculated column to it:

ALTER TABLE PowerAppsTest.deleteme20190603
ADD [DateTimeDate] AS CONVERT(DATETIMEOFFSET(3), [DateTimeStr])

And whenever you need to do calculations in SQL, you can use the DateTimeDate column.

Just another point - your expression to convert between the date and the ISO 8601 format is incorrect for certain time zones. They are not always "on the hour" - the Nepal time zone is 5:45 ahead of UTC, for example. This expression should be more generic:

Text(theDateTime; "yyyy-mm-ddThh:mm:ss.fff") &
    If(TimeZoneOffset(theDateTime) < 0, "+", "-") &
    Text(RoundDown(Abs(TimeZoneOffset(theDateTime)) / 60, 0), "00") & ":" &
    Text(Mod(Abs(TimeZoneOffset(theDateTime)), 60), "00")

Hope this helps!

6 REPLIES 6
cwebb365
Level 10

Re: "Time" to Party? "Time" and "Time" again!

You got it right with the datetimeoffset(7) the dates / time get wrote based on current UTC +/- the current timezone from Powerapps. You need to use dateadd in your view to +/- hours to get the time when using sql queries, but powerapps does it for you based on your timezone on input and reading data from sql. 

Super User
Super User

Re: "Time" to Party? "Time" and "Time" again!

Hi @cwebb365 . Thanks for the reply. Can you clarify the solution a bit? I'm not quite following you. Maybe an example?

PowerApps Staff CarlosFigueira
PowerApps Staff

Re: "Time" to Party? "Time" and "Time" again!

As of today, PowerApps always writes date/time values to SQL using the UTC (+00:00) time zone, regardless of the timezone where the data came from (please create a new feature request in the PowerApps Ideas board to have it preserve the local time zone on those). This is documented in the Data Types page, under the Date/Sql Server section.

If you really want your local time zones preserved, you can use the first approach that you used, saving the date/time value as a string (nvarchar(25)). And to overcome the problem of not being able to do date/time operations without conversions, you can add a new calculated column to your table that does the conversion automatically, so you won't need to do the conversion in every operation.

For example, if this is your table:

CREATE TABLE PowerAppsTest.deleteme20190603 (
	Id INT IDENTITY PRIMARY KEY,
	[Name] NVARCHAR(MAX),
	[DateTimeStr] NVARCHAR(25)
)

You can add a new calculated column to it:

ALTER TABLE PowerAppsTest.deleteme20190603
ADD [DateTimeDate] AS CONVERT(DATETIMEOFFSET(3), [DateTimeStr])

And whenever you need to do calculations in SQL, you can use the DateTimeDate column.

Just another point - your expression to convert between the date and the ISO 8601 format is incorrect for certain time zones. They are not always "on the hour" - the Nepal time zone is 5:45 ahead of UTC, for example. This expression should be more generic:

Text(theDateTime; "yyyy-mm-ddThh:mm:ss.fff") &
    If(TimeZoneOffset(theDateTime) < 0, "+", "-") &
    Text(RoundDown(Abs(TimeZoneOffset(theDateTime)) / 60, 0), "00") & ":" &
    Text(Mod(Abs(TimeZoneOffset(theDateTime)), 60), "00")

Hope this helps!

Super User
Super User

Re: "Time" to Party? "Time" and "Time" again!

Thanks so much @CarlosFigueira . This helps out tremendously on multiple fronts!

Highlighted
Super User
Super User

Re: "Time" to Party? "Time" and "Time" again!

Super User
Super User

Re: "Time" to Party? "Time" and "Time" again!

Tiniest of typos for those following the thread: 

Text(theDateTime; "yyyy-mm-ddThh:mm:ss.fff") & //The " ; " here needs to be a " , " .
    If(TimeZoneOffset(theDateTime) < 0, "+", "-") &
    Text(RoundDown(Abs(TimeZoneOffset(theDateTime)) / 60, 0), "00") & ":" &
    Text(Mod(Abs(TimeZoneOffset(theDateTime)), 60), "00")

Helpful resources

Announcements
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

Top Kudoed Authors
Users Online
Currently online: 176 members 4,783 guests
Please welcome our newest community members: