About a month ago, I found myself transforming data for the reporting portion of our application. Like many reporting systems, ours works off of date ranges, with statistics grouped together by the day (and a few by the hour). An early prototype made heavy use of TSQL Date functions (like DateAdd and DatePart). However, I wasn't happy with this solution, my transformations were unnecessarily coupled to SQL Server. I mean, database date functions all do pretty primitive date arithmetic's, surely we can come up with a way to do that ourselves?
I decided to borrow the strategy long-used by the Unix and PHP community (or JavaScript or doubtless countless other platforms/languages) - to store dates as seconds relative to a starting point. Unix Epoch is January 1st, 1970. If you store seconds since then in an unsigned 32 bit integer, you can store up to 136 years. If you only need minute-resolution, you can store over 8 100 years. If you only need day-resolution, then you can store a bit under 12 million years.
Best of all, you can do straight up arithmetic's against these fields to manipulate the dates. If you want to group hits per user for a day, you can do:
insert into DailyHits (UserId, DaysSince, Count)
select UserId, SecondsSince/86400, count(*)
from Hits
group by UserId, SecondsSince/86400
--86400 is the number of seconds in a day
insert into HourlyHits (UserId, HoursSince, Count)
select UserId, SecondsSince/3600, count(*)
from Hits
group by UserId, SecondsSince/3600
--3600 is the number of seconds in a hour
You can also manipulate relative times in C#, to apply as parameters:
public static readonly DateTime Epoch = new DateTime(1970, 1, 1);
public IList GetDailyHits(DateTime from, DateTime to)
{
command.CommandText = "select * from DailyHits where DaysSince > @From and DaysSince < @To";
command.Parameters.Add("@From", from.Substract(Epoch).TotalDays);
command.Parameters.Add("@To", to.Substract(Epoch).TotalDays);
...
}
If you are using NHibernate, you can use the IUserType interface to help map DateTimes to their relative counterparts (I think).
The examples above are simplistic, but they do work very well and have a number of benefits - the most significant being great independence from the underlying database.
Posted
Thu, Oct 29 2009 9:41 AM
by
karl