Optimal Use of SQL Server Datetimeoffset for Timezone Independent Time Values

With an increasing need for global interactions, understanding timezone-independent time values becomes critical. Using SQL Server datetimeoffset in collaboration with Power Apps offers a comprehensive solution. Dive in to grasp the concept and how you can benefit from it.

Why Use SQL Server Datetimeoffset?

There’s a frequent demand to store datetime values in SQL Server that remain unaffected by time zones. Consider the scenario of scheduling online meetings for global participants. The collaboration of SQL Server and Power Apps facilitates this by storing these values using the datetimeoffset data type.

With Power Apps, the task of converting input time to a timezone-independent value becomes seamless. Misconceptions arise around this topic, with many attempting manual time zone offset calculations. This article clarifies how Power Apps interacts with SQL Server datetimeoffset columns.

Demonstration: Crafting an App for Datetime Storage

Let’s illustrate this by designing a table in SQL Server for meeting schedules. The crux is configuring the ‘meeting date’ column with the datetimeoffset data type.

Upon integrating with Power Apps, one can establish screen controls and append a fresh record to the table using the following method:

    Patch(Meeting, 
          Defaults(Meeting),
          {
             Description:txtDescription.Value, 
             MeetingDate:DateTimeValue(txtMeetingDateTime.Value)
           }
    )
  

Reference:Patch function 

This command introduces a new record to the Meeting table based on user input.

Analysing User Record Addition

From a system with the Windows time zone set to Pacific time (UTC – 8hrs during this analysis), adding a record aiming for 3 PM PST results in Power Apps using the device’s time zone to deduce the offset, transferring the UTC value to SQL Server (e.g., 2021-12-09T23:00:00.000z).

Record Display Post Addition

Displaying the recently added record is achievable by adding a label with this formula:

    "Latest Meeting: " & Last(Meeting).MeetingDateTime
  

On a Pacific time-zoned system, this displays the anticipated local time of 15:00.

Record Viewing from Alternate Time Zones

Observing from a computer with UK/GMT time zone (UTC+/-0) displays 23:00, which is the accurate local time.

Conclusion

The necessity of storing timezone-independent time values in SQL Server is efficiently addressed with the datetimeoffset data type. This guide elucidated its application and underlined the automatic time value conversions by Power Apps based on the user’s device time zone, eliminating any extra effort from the developers.

Need assistance or have queries about this topic? Don’t hesitate to contact us. We’re here to guide and serve your technical needs, ensuring you get the best out of these technologies.