Blog
SQL - Storing timezone independent time values with datatimeoffset
In cases where it's necessary to store timezone independent time values in SQL Server, we can accomplish this by using the datetimeoffset data type. This post describes how this data type works with Power Apps.
When we add, edit, or retrieve records against columns of this data type, Power Apps automatically manages the task of converting the input time to time zone independent value. The process is straightforward because there is nothing more that we need to do in Power Apps.
However, this topic can cause confusion, and I occasionally see app builders who attempt to implement this type of requirement by manually calculating the time zone offset. Therefore, this post provides a description of Power Apps deals with SQL Server datatimeoffset columns.
Demonstration - Building a sample app to store datetime values
To demonstrate this topic, we'll create a table in SQL Server to store meeting times. The key step is to set the 'meeting date' column to the data type datetimeoffset as shown in the screenshot beneath.
From Power Apps, we can now create controls on a screen, and patch a new record to the table like so:
Patch(Meeting,
Defaults(Meeting),
{
Description:txtDesc.Text,
MeetingDate:DateTimeValue(txtMeetingTime.Text)
}
)
This formula adds a new record to the Meeting table based on the values that the user enters in the text input controls.
What happens when a user adds a record?
Let's examine what happens when a user adds a record. From a computer with the Windows time zone set to Pacific time (UTC - 8hrs at the time of writing), this is what happens when we attempt to add a record with a target time of 3 PM PST.
In the call to Patch, Power Apps uses the time zone setting on the user device to calculate the offset, and it passes the UTC value to SQL Server (ie, 2021-12-09T23:00:00.000z). We can see this when we monitor the app.
Following the addition of the record, we can inspect the value in SQL Server and confirm that our formula saves the date time value correctly (e.g. with a UTC time element of 23:00)
What happens we view the record that we added?
Let's now display the record that we created by adding a label with the following formula:
"Latest Meeting: " & Last(Meeting).MeetingDate
On our computer with the time zone set to Pacific time, the label displays the expected local time of 15:00.
What happens when a user from a different time zone views the record?
Let's now attempt the same thing from a different computer with the time zone set to the UK/GMT (UTC+/-0).
As the screenshot beneath highlights, the label shows 23:00, which is the correct local time.
![](/posts/files/e911efa3-0f28-4efb-8fbc-e67840a6af1e.png)
Conclusion
- Categories:
- sql datetime