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.

There are often cases where we want to store date time values in SQL Server which are independent of time zone. An example is the storage of meeting times relating to online meetings for a global audience. With SQL Server and Power Apps, we can easily accomplish this by storing the date time values in a column of data type datetimeoffset.

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.

Conclusion

In cases where it's necessary to store timezone independent time values in SQL Server, we can accomplish easily with the help of the datetimeoffset data type. This post described how to use this data type, and highlighted how Power Apps automatically converts time values to match the time zone of the end-user computer/device, and how it does this without the need for app builders to carry out any additional tasks or configuration.
Previous
Next
Related posts