December 5, 2011 at 7:20 am
What is recommended/best practice when it comes to storing date/time for an application that needs to support multiple languages?
Is it best to use the SQL DateTime data type and insert date/time as UTC e.g. convert local time to UTC in application business layer and pass to DB already in UTC format?
or
Should I use the SQL DateTimeOffset data type which will record the offset relative to UTC?
December 5, 2011 at 7:23 am
What is recommended/best practice when it comes to storing date/time for an application that needs to support multiple languages?
I assume you mean multiple timezones not languages? It is best if you store all datetimes consistently. Most often it makes the most sense to use UTC date but being consistent I think is more important. As you said, the business layer is the best place to convert to local time.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 5, 2011 at 7:27 am
The application needs to support multiple languages and therefore timezones. So the application will take care of displaying date/time in the correct format based on locale.
However, I'm not sure what the advantages/disadvanatges are for storing as UTC using datetime or local time using datetimeoffset?
December 5, 2011 at 7:40 am
In any case you need to do Date Mathematics to get the local date (or UTC Date).
Scenario 1:
A business house may have Business Hours defined. They need to be calculated on Local Datetime.
Scenario 2:
In an E-commerce application, you might need to Transaction Time (local) as well as UTC. Example: Online Money Transfer between two accounts, one in US & one in India.
You need to identify the pattern of date people referred most & which would be your base Datetime. Also it’s a good idea to have a lookup for your date dimensions with time zones defined.
December 5, 2011 at 7:41 am
Languages and timezones are not the same thing. You can two people standing next to each other that speak different languages but are obviously in the same timezone. Or you can have two people that speak the same language and opposite sides of the globe. The latter two people are in different timezones.
I guess the way you store your date information will be somewhat dependent on how you are using it. Personally I like to store everything in UTC along with the timezone offset. The big advantage there is that you don't have to try to do oddball calculations for daylight saving time and such. If you know the universal time and the offset from that as it relates to your end user you can pretty easily reassemble anything around the time that you need.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 5, 2011 at 7:53 am
I understand the difference between language and timezone, I was just giving some background to the application. The application will be storing details of customer transactions from different countries so the UI will be localized and therefore require dates be displayed in local time also.
December 5, 2011 at 7:59 am
Sean Lange (12/5/2011)
I guess the way you store your date information will be somewhat dependent on how you are using it. Personally I like to store everything in UTC along with the timezone offset. The big advantage there is that you don't have to try to do oddball calculations for daylight saving time and such. If you know the universal time and the offset from that as it relates to your end user you can pretty easily reassemble anything around the time that you need.
Not sure I follow. Doesn't the timezone offset depend on DST?
December 5, 2011 at 7:59 am
Well then much like your transactions it is most important that you store all your data in the same base (timezone or currency). Then you figure out how to display it on demand. Like I said before, my preference would be to store it in UTC along with a timezone offset at the time of the transaction (or even local time).
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 5, 2011 at 8:01 am
bwoulfe (12/5/2011)
Sean Lange (12/5/2011)
I guess the way you store your date information will be somewhat dependent on how you are using it. Personally I like to store everything in UTC along with the timezone offset. The big advantage there is that you don't have to try to do oddball calculations for daylight saving time and such. If you know the universal time and the offset from that as it relates to your end user you can pretty easily reassemble anything around the time that you need.Not sure I follow. Doesn't the timezone offset depend on DST?
Yes. That way you know what time the transaction occurred in UTC in addition to the local time at the time of the transaction. The fact that is changes is exactly the reason to capture it. 🙂
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 5, 2011 at 8:20 am
Sean Lange (12/5/2011)
bwoulfe (12/5/2011)
Sean Lange (12/5/2011)
I guess the way you store your date information will be somewhat dependent on how you are using it. Personally I like to store everything in UTC along with the timezone offset. The big advantage there is that you don't have to try to do oddball calculations for daylight saving time and such. If you know the universal time and the offset from that as it relates to your end user you can pretty easily reassemble anything around the time that you need.Not sure I follow. Doesn't the timezone offset depend on DST?
Yes. That way you know what time the transaction occurred in UTC in addition to the local time at the time of the transaction. The fact that is changes is exactly the reason to capture it. 🙂
I think I follow...having UTC and the transaction local time gives you everything you need to calculate any specific time needed.
I don't mean to hijack the thread but the reason this topic piqued my interest is because I'm currently trying to fix a poorly designed application that saves a transaction date from all different time zones using the local DB server time. So the developers had to come up with a way of doing all those oddball calculations you mentioned. Which time zone the transaction is from, is the date during DST, correct offsets etc.
Real pain in the butt...
December 5, 2011 at 8:35 am
bwoulfe (12/5/2011)
Sean Lange (12/5/2011)
bwoulfe (12/5/2011)
Sean Lange (12/5/2011)
I guess the way you store your date information will be somewhat dependent on how you are using it. Personally I like to store everything in UTC along with the timezone offset. The big advantage there is that you don't have to try to do oddball calculations for daylight saving time and such. If you know the universal time and the offset from that as it relates to your end user you can pretty easily reassemble anything around the time that you need.Not sure I follow. Doesn't the timezone offset depend on DST?
Yes. That way you know what time the transaction occurred in UTC in addition to the local time at the time of the transaction. The fact that is changes is exactly the reason to capture it. 🙂
I think I follow...having UTC and the transaction local time gives you everything you need to calculate any specific time needed.
I don't mean to hijack the thread but the reason this topic piqued my interest is because I'm currently trying to fix a poorly designed application that saves a transaction date from all different time zones using the local DB server time. So the developers had to come up with a way of doing all those oddball calculations you mentioned. Which time zone the transaction is from, is the date during DST, correct offsets etc.
Real pain in the butt...
That is a real pain. What I am saying is if you know either the local time OR UTC AND the difference between local and UTC you should be able to exactly determine the global time for anything. Basic algebra. A + B = C. If you know any two values the third is easy to determine.
UTC_Time + UTC_Offset = LocalTime.
select getutcdate() as UTC_Date, getdate() as LocalTime, DATEDIFF(hh, getutcdate(), getdate()) as UTC_Offset
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 5, 2011 at 8:43 am
UTC_Time + UTC_Offset = LocalTime.
I have another equation. 😉
UTC_Time = LocalTime +/- UTC_Offset
The difference here is you are taking UTC as base where as I am taking LocalTime as base (just for example).
As I already said, we can't escape from Date Mathematics in any situation. OP needs to decide, what should be the base date format based on regular transactions.
December 5, 2011 at 8:52 am
Dev (12/5/2011)
UTC_Time + UTC_Offset = LocalTime.
I have another equation. 😉
UTC_Time = LocalTime +/- UTC_Offset
The difference here is you are taking UTC as base where as I am taking LocalTime as base (just for example).
As I already said, we can't escape from Date Mathematics in any situation. OP needs to decide, what should be the base date format based on regular transactions.
Agreed. I find it easier to store everything in the same timezone (UTC date). The primary advantage is you can do some date manipulation on the whole set instead of each row. Things like "what time of day is the most common for a certain type of transaction" can be really painful when stored in local time. But pretty straight forward when all the data is consistent. You find the time that meets your criteria and then convert it. It is kind of like fractions. If you store your data in local time you first have to convert it all to the same common denominator. Then you can evaluate it. If it is already consistent it eliminated the extra step and mass confusion.
This is the same type of logic used in multi-currency systems. It is often the best practice to store all values in a single currency (it doesn't really matter which currency as long as it is consistent) along with the actual currency and the exchange rate at the time of the transaction. Makes querying the data tons easier.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 5, 2011 at 9:08 am
Sean Lange (12/5/2011)
Dev (12/5/2011)
UTC_Time + UTC_Offset = LocalTime.
I have another equation. 😉
UTC_Time = LocalTime +/- UTC_Offset
The difference here is you are taking UTC as base where as I am taking LocalTime as base (just for example).
As I already said, we can't escape from Date Mathematics in any situation. OP needs to decide, what should be the base date format based on regular transactions.
Agreed. I find it easier to store everything in the same timezone (UTC date). The primary advantage is you can do some date manipulation on the whole set instead of each row. Things like "what time of day is the most common for a certain type of transaction" can be really painful when stored in local time. But pretty straight forward when all the data is consistent. You find the time that meets your criteria and then convert it. It is kind of like fractions. If you store your data in local time you first have to convert it all to the same common denominator. Then you can evaluate it. If it is already consistent it eliminated the extra step and mass confusion.
This is the same type of logic used in multi-currency systems. It is often the best practice to store all values in a single currency (it doesn't really matter which currency as long as it is consistent) along with the actual currency and the exchange rate at the time of the transaction. Makes querying the data tons easier.
I agree to you if transactions distribution is uniform. It really makes sense to store them in UTC. I would go for it & there is no point-of-view difference.
Now consider a scenario of Multi National Company. Their presence in 3 countries but 90% of their transactions are done in its home country. In this case I would go for LocalTime to save 90% unnecessary time calculations (on the cost of Future Growth of Company, risking flexibility)
December 5, 2011 at 9:11 am
I agree to you if transactions distribution is uniform. It really makes sense to store them in UTC. I would go for it & there is no point-of-view difference.
Now consider a scenario of Multi National Company. Their presence in 3 countries but 90% of their transactions are done in its home country. In this case I would go for LocalTime to save 90% unnecessary time calculations (on the cost of Future Growth of Company, risking flexibility)
Yes, the important thing is consistency. It doesn't matter from a logic perspective which value is the "base". But it DOES matter if the base changes. That is when you get into a pickle. 😀
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply