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!
Solved! Go to 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!
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.
Hi @cwebb365 . Thanks for the reply. Can you clarify the solution a bit? I'm not quite following you. Maybe an example?
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!
Thanks so much @CarlosFigueira . This helps out tremendously on multiple fronts!
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")
User | Count |
---|---|
179 | |
120 | |
87 | |
44 | |
41 |
User | Count |
---|---|
245 | |
156 | |
127 | |
77 | |
73 |