Migration from Oracle to azure SQL caveat — Azure SQL does not support time zone settings
In this post, I continue to share what I have learned while working with my team to migrate our application and database from Oracle to azure SQL. Specially, I share an issue we encounter with time zone in azure SQL.
Our Oracle database and application servers all have the same time zone, which is PST. During the process of migrating from oracle to azure SQL, I learned that azure SQL default time zone is UTC, and it’s not possible to change the time zone. In our system, timestamp values can come from one of our applications, or the database itself. As a result, we could have timestamp values of mixed time zones. In the azure SQL database, we use DateTime data type, which does not provide time zone support.
Azure SQL Database does not support time zone settings; it always follows UTC. Use AT TIME ZONE in SQL Database if you need to interpret date and time information in a non-UTC time zone.
One way to handle the mixed time zones issue is refactoring the applications as well as the database to store time zone info along with timestamp values. This way, our applications can convert a timestamp value coming from the database to the local time zone, given the time zone of the timestamp. However, this approach is overkill since we don’t have the business need to support globalization.
Another approach is to handle the time zone from the client applications. For us, we just need to ensure all time stamp values are in PST. Since our applications run on azure app services, all we need to do is adding the WEBSITE_TIME_ZONE property under Application Settings via azure portal. For more info, checkout the document which shows the exact steps to update the time zone.
The problem occurs when we we have a query that compares a date and time with the current time by calling one of the built in SQL functions such as SYSDATETIME, GETDATE or CURRENT_TIMESTAMP. The built in functions generate timestamp values with UTC time zone. As a workaround, we use the AT TIME ZONE function to convert to the time zone to PST, as shown in the snippet below:
ALTER FUNCTION dbo.fn_GetCurrentDateTime_PST()
RETURN (SELECT SYSDATETIMEOFFSET() AT TIME ZONE 'Pacific Standard Time')
I also learned that as an alternative to azure SQL, we could use azure SQL managed instance, which allows setting the time zone for the entire SQL server instances.
Originally published at https://www.taithienbo.com on June 4, 2022.