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.

Time zones in Azure SQL Managed Instance

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. To work around this issue, 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() 
RETURNS DATETIME 
AS
BEGIN

RETURN (SELECT SYSDATETIMEOFFSET() AT TIME ZONE 'Pacific Standard Time') 
END
GO

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.

References

Azure SQL Managed Instance time zones – Azure SQL Managed Instance | Microsoft Docs

Lesson Learned #4: Modifying the default time zone for your local time zone. – Microsoft Tech Community