asked
How to Handle Time Zones
Hi, Currently we’re using Merge Replication (Push and Pull) for our application. We’ve our Publisher DB located at EST time zone. And all 53 subscribers were located in different time zones. 14 subscribers were located at EST time zone 23 subscribers were located at PST time zone 12 subscribers were located at MST time zone 4 subscribers were located at CST time zone Now we’re planning to use centralized DB with “Always ON” feature in SQL Server 2012 for our application. But we’re having problem because of different time zones. We would like to do minimal changes to our application to handle time zone. My Plan: To update all the related tables with proper Date and time using Stored procedure and function as we’ve used Now statement at many places. I don’t think Triggers is the best solution as I’ll need to update minimum 25 tables at a time. And 100 users will do the same transactions at the same time. Can you please provide suitable solution for these scenario?
I would look at using `GETUTCDATE` rather than `GETDATE`. See
http://msdn.microsoft.com/en-us/library/ms178635.aspx GetUTCDate stores the Universal Time (GMT) - ie without any time zone.
While I agree with Thomas that using GMT is the cleanest solution, there are times that it makes sense to store the data of which timezone the event took place in. To do that, just add another column and store the standard abbreviation for the timezone. Many datetime libraries are timezone aware so as long as you pass the information along they can adjust in any calculations that need to be made and depending on the need they can adjust to display the data in whatever timezone the human viewing the data will be most comfortable with.
10 People are following this question.
