In this article we are going to discuss how to fix incorrect Mexico Time in SQL Server sys.time_zone_info system catalog.
Problem:
One of our customer informed that Mexico changed their DST . In UAT, application is showing correct time which is UTC -6 however in Production the mexico time zone is showing as UTC – 5. The application is referring the sys.time_zone_info system catalog in SQL Server.
Troubleshooting:
In the first place you should understand what is the actual problem. In other words what the customer is talking about. The problem statement it is not very clear if it is SQL Server issue or OS issue or it is something to do with application code.
After a short call with the customer we got to know that as per Mexican Government order published in October 26th, 2022, that Daylight Saving Time (DST) in the United Mexican States will not be observed in 2023 calendar year.
Second is to compare the UAT and Production environment.
- No change in code at application end.
- There is no difference in OS time zone settings in application and database server.
- The result of Mexico Time Zone in sys.time_zone_info were different.
Query to check the Mexico Time Zone information:
SELECT * FROM sys.time_zone_info
WHERE name LIKE '%Mexico%'
Result:
The above result was from UAT where the output is showing correct. Likewise we checked it in Production environment and result is as follows:
Certainly we could see the difference as mentioned by the customer. What next?
- Checked the SQL Server patch and both UAT and Production Instance were in the same patch level
- Compared the OS patch level on both the servers and we could find the difference in patches installed
Being DBA you need to know how to verify these stuffs to be able to quickly identify the issue. Below screen shot will help you with the path:
The highlighted patch was not installed in Prod because it was scheduled for next month patching cycle.
A quick read about the patch in the KB article KB5023702 clarifies the issue:
Resolution & Conclusion:
The resolution is a no brainer once you find the issue. You have to follow the process as per your organization and get the patch installed in the server.
To conclude incorrect time zone in sys.time_zone_info is not a SQL Server issue. However being DBA you should be able to troubleshoot, find the root cause and suggest the solution. If customer complains about one server shows correct time zone while the other is incorrect without doing any changes in the application end, you should perform a thorough check at server end. The issue occurred because of not having latest OS patch and you can resolve it by going through the relevant article of the OS patch and get it installed.
