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

[ericonline]

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.

Any ideas are appreciated. THank yoU!

[cwebb365]

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. 

[ericonline]

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

[CarlosFigueira · Accepted solution]

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!

[ericonline]

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

[ericonline]

Idea posted here.

[ericonline]

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