Advice needed for Querying DateTimeOffset data by end users

Question

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.

Answer 1

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