Sponsored By Aspose - File Format APIs for .NET

Aspose are the market leader of .NET APIs for file business formats – natively work with DOCX, XLSX, PPT, PDF, MSG, MPP, images formats and many more!

Eschewing Date Types in our Database

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.

This entry was posted in Uncategorized. Bookmark the permalink. Follow any comments here with the RSS feed for this post.

13 Responses to Eschewing Date Types in our Database

  1. Matt Spradley says:

    This is not a good idea as so many other people have already mentioned.

    I used to convert data from several medical information systems written in everything from BASIC, COBOL, C++, FoxPro, etc.

    Several of the older systems, particularly the COBOL systems, would brew there own date type. They would always have errors around subtle issues like leap years etc.

    Dates are hard. That is why modern systems implement them so we don’t have to. And as someone else mentioned, that date arithmetic built into the date support for a given system is what makes reporting easy.

    Even if you had to convert the system do a different database, which in practice rarely happens, most date functionality is similar between systems and a conversion would be trivial.

  2. Pneumatic says:

    great article.

  3. Rui says:

    How about Monthly Hits?

  4. Petar Repac says:

    @karl
    And we also learn (and a lot) from you :))
    Thanks for your blog posts

  5. Steve Allen says:

    Beware with epoch times, for not all epochs are sensibly defined, and different jurisdictions disagree on what they are supposed to be counting. See
    http://www.ucolick.org/~sla/leapsecs/epochtime.html

  6. karl says:

    I blog as much to teach as to learn, thanks for all the feedback.

  7. Andrew says:

    This reaks of being too smart for you own good.

  8. I don’t know, seems to me like date calculations are anything but simple and that you’re going to have to re-solve in your application code a lot of problems that have been more reliably solved by the database engine itself. Plus all the problems others have already mentioned.

  9. It’s interesting that you’re doing this for a reporting app. That would be the primary reason I would NOT do it. Large corporations often have a reporting group that will use SSRS or some other reporting software. Those groups tend to frown on storing data that requires custom software to retrieve.

  10. Petar Repac says:

    This doesn’t seem like a good idea to me:
    * you are making hard for anyone that looks directly in the db to see actual data
    * looks like you had coupling with logic in db not the data itself, why not read dates in .net app and then perform necesary calculations ?
    * databases ARE different, you better accept this, so DB independence will always have it’s limits

    Regards,
    Petar

  11. Patrice Calvé says:

    Internally, Sql already stores dates as “relatives”. They are stored as the number of days since January 1st 1900, and the fraction is the time of day. (0.5 is January 1st 1900, at noon)

    DECLARE @D1 as datetime
    DECLARE @D2 as datetime
    DECLARE @D3 as datetime

    SET @D1 = ‘2009.10.11 12:13:14′
    SET @D2 = ‘2010.10.11 12:13:14′
    SET @D3 = 0.5

    PRINT @D1
    PRINT @D2
    PRINT @D3
    PRINT @D2-@D1

    PRINT Cast(@D1 as integer)
    PRINT Cast(@D2 as integer)
    PRINT Cast(@D3 as integer)
    PRINT Cast(@D2-@D1 as integer)

  12. Enoc Pardue says:

    This seems unnecessary, as the justification for not being tied to an underlying database representation becomes moot when you consider that interfacing any two systems requires data interpretation, and that there is no such thing as a universal data filter. This approach, therefore, only seems to introduce more maintenance for and dependency on the programmer. What supposed other benefits are so compelling about this approach?

  13. Peter Hart says:

    …except that there aren’t 86400 seconds in a day. Some days there are only 23*60*60 seconds (when daylight savings time hits us in the Spring). Some days there are 25*60*60 seconds (Fall transition), and other days (on which a leap second http://en.wikipedia.org/wiki/Leap_second occurs), there may be 86401 or 86402 seconds (I believe). Not a big deal for many uses of this approach, but important to point out that if you really want to know what happened on a given day (rather than an approximation) you have to be careful with this approach….