I've been storing all dates in the database as UTC time for several years now, but recently, it's becoming more of a pain to deal with date range queries in reporting services where the date range is specified in local time.
Specifically, let's take this example data set:
EmailAddress DateCreated
random_email@example.com 2009-09-01 00:00:00
random_email@example.com 2009-09-01 00:00:00
random_email@example.com 2009-02-28 04:00:00
random_email@example.com 2009-04-27 14:33:00
random_email@example.com 2009-08-31 17:28:00
random_email@example.com 2009-03-19 18:57:00
rand开发者_如何转开发om_email@example.com 2009-03-01 00:49:00
random_email@example.com 2009-02-28 04:00:00
random_email@example.com 2009-09-01 00:00:00
random_email@example.com 2009-09-16 00:00:00
Now let's say the client wants to see how many records exist for the month of February using central standard time. I cannot simply query for records using UTC time, because the UTC timestamp must be converted to CST before the grouping takes place. In other words, record #7 (2009-03-01 00:49:00) should be counted under February using CST dates, even though the UTC date puts it squarely in the month of March.
What usually ends up happening is that I write a function to convert the date such that a query looks like so:
select
dbo.ConvertToLocalDate(DateCreated),
count(*) as [Count]
from
example_table
group by
dbo.ConvertToLocalDate(DateCreated)
but the performance of such a query is less than desirable for anything more than a few hundred thousand rows. I've tried several variations on the theme, including adding a column to pre-calculate the local date, and also a modified query such as:
select
t3.LocalDateCreated,
count(*) as [Count]
from
example_table t1
inner join (
select
t2.Email,
dbo.ConvertToLocalDate(t2.DateCreated) as LocalDateCreated
from
example_table t2) t3 on t3.Email = t1.Email
group by
t3.LocalDateCreated
This all seems really hackish to me though. Is there a better way?
Use a persisted computed column in your table to store the Local Date, and then index it and use it in your query.
That way, the value will be calculated when the value is inserted, but you don't need to do the maths yourself anywhere.
Also, read my blog post about Scalar Functions at http://msmvps.com/blogs/robfarley/archive/2009/12/05/dangers-of-begin-and-end.aspx - you may want to rethink the way you're using that function.
精彩评论