Time zone in SQL Server
-
I am onto a project of backoffice management of International stock exchanges. I need to manage trades of different stock exchanges there. The date and time I am getting from exchange is according to their time zone. Further I need to calculate some thing using their time. Some of reports I need to generate after converting the time to my time zone that is IST (I am from India). Now what I have in my mind is to store the time provided by the exchange with the time zone (of the exchange) and also the converted (to IST) time. I am looking for some idea how to store the time zone and the converted time into database. Is there any facility in SQL server to time between time zones or I have to keep the time gap between zones? I have found SQL 2005 Time Zone Conversion Functions[^] and http://msdn.microsoft.com/en-us/library/ms145276%28SQL.90%29.aspx[^] But, what would be the best practice to do such thing?
-
I am onto a project of backoffice management of International stock exchanges. I need to manage trades of different stock exchanges there. The date and time I am getting from exchange is according to their time zone. Further I need to calculate some thing using their time. Some of reports I need to generate after converting the time to my time zone that is IST (I am from India). Now what I have in my mind is to store the time provided by the exchange with the time zone (of the exchange) and also the converted (to IST) time. I am looking for some idea how to store the time zone and the converted time into database. Is there any facility in SQL server to time between time zones or I have to keep the time gap between zones? I have found SQL 2005 Time Zone Conversion Functions[^] and http://msdn.microsoft.com/en-us/library/ms145276%28SQL.90%29.aspx[^] But, what would be the best practice to do such thing?
The best method for dealing with data from different time zones is to store all time values as UTC times (i.e. zone 0). Where necessary keep also a note of the time zone offset for the record, or the particular account customer. You can then calculate time differences in absolute terms when trying to sort transactions into time order etc. When producing reports for consumers you should add their personal time zone offset to the time values, so the consumer sees report data in their own time zone.
It's time for a new signature.
-
I am onto a project of backoffice management of International stock exchanges. I need to manage trades of different stock exchanges there. The date and time I am getting from exchange is according to their time zone. Further I need to calculate some thing using their time. Some of reports I need to generate after converting the time to my time zone that is IST (I am from India). Now what I have in my mind is to store the time provided by the exchange with the time zone (of the exchange) and also the converted (to IST) time. I am looking for some idea how to store the time zone and the converted time into database. Is there any facility in SQL server to time between time zones or I have to keep the time gap between zones? I have found SQL 2005 Time Zone Conversion Functions[^] and http://msdn.microsoft.com/en-us/library/ms145276%28SQL.90%29.aspx[^] But, what would be the best practice to do such thing?
-
The best method for dealing with data from different time zones is to store all time values as UTC times (i.e. zone 0). Where necessary keep also a note of the time zone offset for the record, or the particular account customer. You can then calculate time differences in absolute terms when trying to sort transactions into time order etc. When producing reports for consumers you should add their personal time zone offset to the time values, so the consumer sees report data in their own time zone.
It's time for a new signature.
Yes I agree. Actually I was in a bit dilemma because I will get the data from exchange side (they are providing flat files) will be in their time zone. So I need to convert it into UTC first. But in some day end calculations I need to use their time also, so in that time I need to convert it again into their time zone. However perhaps I will store it in UTC format ultimately. Thank you Richard
-
While saving the time from another time zone, just save value returned by
GetDatefunction. It gives current time in the current timezone. In case you are looking for GMT times, useGetUtcDatefunction.Yes thats right. But I dont want to store the current time. I need to use the time provided by the exchange. Thats why I was thinking what to do Thank you nish.
-
Yes I agree. Actually I was in a bit dilemma because I will get the data from exchange side (they are providing flat files) will be in their time zone. So I need to convert it into UTC first. But in some day end calculations I need to use their time also, so in that time I need to convert it again into their time zone. However perhaps I will store it in UTC format ultimately. Thank you Richard
As a general rule you should never store times in a localtime format, they should always be stored as UTC values. This allows you to manipulate them in any way necessary, time difference, sorting etc. It is only necessary to convert to localtime when you wish to display it for the end user irrespective of what time zone that user is in.
It's time for a new signature.
-
As a general rule you should never store times in a localtime format, they should always be stored as UTC values. This allows you to manipulate them in any way necessary, time difference, sorting etc. It is only necessary to convert to localtime when you wish to display it for the end user irrespective of what time zone that user is in.
It's time for a new signature.
OK. thank you.