In the computing world, time zones have a very important place. Imagine a news website where content is published from two different places. If the time zones are not synchronized, the order in which stories may appear may be incorrect. Or you can consider the case of a bid based shopping site which gets accessed from different regions. If time zones are not factored in, some people from a certain location may get the idea that the bid closing time mentioned is their local time and end up surprised when they are unable to place a bid.
Time zones incidentally play a critical part in any application that is accessed from different locales which fall in varied time zones. The SQL Server database until its 2008 iteration completely seemed to have left the issue of tackling time zones at the mercy of the developer. In fact a decade back, a number of large companies were reluctant to try out the application owing to such shortcomings. However things have improved considerably since then and the “DateTimeOffset” data type has made the life of developers quite easy. Further in collaboration with corollary function, time zones can be neatly handled in SQL Server.
- How SQL Server Handles Time Zones
The “DateTimeOffset” data type typically uses UTC time internally and reflects all time data in the same format. A developer can quickly set time zones as per his preference and scope of the application. The difference in time zones is handled with the help of an offset which moves the time from base UTC to the corresponding degree.
- Advantages of handling time zones in the database
When you let the SQL database handle the time zone conversion process, you need to consider the fact that it would go a long way in making your database application a lot simpler. Moreover the output would be always consistent and different programs can access the same data without bothering to make changes.
- Current Drawbacks
The SQL Server database still does not adequately address day light saving time changes and rather lives under the belief that the developers would factor the same. However with 2014 iteration life may just become better as it is claiming to offer support for conversion to local time zone which would consider DST.
- Things to keep in mind while an enterprise wide SQL Server implementation
When you are running an enterprise wise SQL Server implementation you need to lay considerable emphasis on your hardware and backup procedures. Always make it a point to plan out your hardware in a manner that you can scale up or scale out at a rapid pace. Next you need to enforce advanced backup procedures and implement a minimum RAID 5 standard. Last but not the least always keep a sql recovery tool like DataNumen SQL Recovery handy to deal with a possible SQL crash where your critical data may get messed up.
Alan Chen is President & Chairman of DataNumen, Inc., which is the world leader in data recovery technologies, including access recovery and sql recovery software products. For more information visit https://www.datanumen.com/