Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We are building an Operational Data Store (ODS). The data is coming from our production databases which are located in the United States and Asia. The data in our production databases store date/time values as local time (no UTC or offsets). When we bring the data into our ODS, we're storing all date/time values as DateTimeOffset values.

When our users query the ODS data, we don't want them to have to think about including the appropriate offset value in their queries involving date/time values. We want them to be able to query the data as if it were simple local time values.

We're masking the offset values on date/time values by having users query views. The views return local time values and strip off the offset values. While this works, any indexes on the underlying datetimeoffset columns are not used because the act of converting the datetimeoffset to datetime in the views causes indexes to not be used.

So, I'm looking for advice on how others, who have dealt with this issue, have dealed with it. We need to be able to utilize indexes while, at the same time, not require users to think about time zones and daylight savings time when querying the data.

We're using SQL Server 2012.

share|improve this question
1  
Store as UTC. Then everything is at par and you can always convert to local time zone when you need to (and the presentation side can handle that, as well as handle converting seek parameters from the client's time zone). One weakness you may not have noticed yet abut DateTimeOffset: it is not DST-aware. –  Aaron Bertrand Aug 20 '13 at 14:36
    
@AaronBertrand - Thanks. I don't see how storing UTC solves my problem. When users query the ODS UTC data they would need to know how to convert the UTC values to local time to query the data correctly. If a user wants to see all rows where some value is, say, greater than 8/20/2013 5:00:00 AM, they are thinking in terms of local time, but the data is UTC. They would have to convert their query value to UTC first. Or, the UTC needs to be converted to local time, but then any index on the column won't get used. –  Randy Minder Aug 20 '13 at 14:45
    
Can't the application convert for them? It knows the difference between their time zone and UTC. Another idea is to use a calendar table, which is a solution I used in the past for an application developed long before DateTimeOffset even existed, and when time zone handling in C# was much less mature. You have a function that returns the offset based on TimeZone and Date, and add/subtract the number of minutes before passing that value to the actual query. The user doesn't have to think. –  Aaron Bertrand Aug 20 '13 at 14:47
    
@AaronBertrand - There is no application. This is an ODS that users will be querying directly for ad-hoc queries, reporting, and other sorts of data analysis. –  Randy Minder Aug 20 '13 at 14:49
2  
And, again, you're not going to be able to use datetimeoffset to magically solve this, and particularly without some additional work, unless you don't care about DST shifts. –  Aaron Bertrand Aug 20 '13 at 15:09
show 4 more comments

migrated from stackoverflow.com Aug 20 '13 at 15:40

This question came from our site for professional and enthusiast programmers.

1 Answer

We solved this in Oracle using function based indexes. Although, it's not quite as flexible, you can accomplish similar in SQL Server using computed columns.

If all of the information you need is in the table, and you meet all of the ownership requirements (outlined in the second article) then I would recommend making the date/time field a computed column on the table, and then add an index to the computed column.

Computed Columns:

http://technet.microsoft.com/en-us/library/ms191250(v=sql.105).aspx

Indexing Computed Columns:

http://msdn.microsoft.com/en-us/library/ms189292

share|improve this answer
1  
So are you recommending a computed, indexed column for every possible timezone any user might be in? –  Aaron Bertrand Aug 20 '13 at 14:50
    
Interesting. This might work. I'll have to study this. –  Randy Minder Aug 20 '13 at 14:52
    
@Aaron Bertrand: Yes, that's correct. In our case, there were only 2 possibles. ET and GMT, so that was manageable. –  McSenstrum Aug 20 '13 at 14:53
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.