Incercept when DateTime.Now is used when writing to a Database

Question

It is well known that in a database, you should be storing the date in universal time coordinate.

I am looking for a way to know when a developer misuses DateTime.Now when writing to the Database.

We are using Sql Server 2008, with either EF4 or nHibernate 3.0.

Is it possible to intercept the value of a datetime when it contains time zone information at a low level layer, like either in Sql Server, or in NH/EF?

Your "well known" fact is itself a little subjective. While a universal time is probably a good *default*, there are times when a local time is both more convenient and more appropriate. — Marc Gravell, Jun 03 '11 at 18:01
No, it is not always correct to store the time in UTC. For example, a bus or train timetable does not make sense in UTC, but should be stored in local time. The same applies to appointments. — mnemosyn, Jun 03 '11 at 18:01
For an in-depth discussion of UTC vs. local time, also see http://stackoverflow.com/questions/2580478/storing-date-times-as-utc-in-database. Keep in mind that datetime does not contain the actual time zone information, but only a flag that says `Local, Utc` or `Unspecified`. — mnemosyn, Jun 03 '11 at 18:08
@mnemosyn when a bus or train crosses a timezone, what constitutes "local time"? — D'Arcy Rittich, Jun 03 '11 at 18:09
@RedFilter: the thread I refer to discusses some issues. Certainly, local time isn't always reversible because of DST: ambiguities might arise. Still, an appointment that is set to 7pm reamins at 7pm, even if its shifted one week (across a DST border, for example), and it could be very tricky to apply these rules correctly -- hence, in rare cases, local time makes things easier. — mnemosyn, Jun 03 '11 at 18:14
In general, if you’re going to store DateTime values in database or perform calculations on such values, it’s better to use UtcNow because in the former case, this helps you have a universal value regardless of the local time of the machine where you host your program and in the latter case there is no difference between the duration of time calculated by Now and UtcNow. http://aspalliance.com/2062_The_Darkness_Behind_DateTimeNow — Dariusz, Jun 03 '11 at 18:58

Diego Mijelshon · Accepted Answer · 2011-06-05T11:56:08.493

You can add the following event listener:

public class DateTimeEventListener : IPreUpdateEventListener,
                                     IPreInsertEventListener
{
    public bool OnPreUpdate(PreUpdateEvent e)
    {
        foreach (var value in e.State)
            if (value is DateTime && ((DateTime)value).Kind != DateTimeKind.Utc)
                throw new Exception("Non-UTC DateTime used");
    }

    public bool OnPreInsert(PreInsertEvent e)
    { /*Same as OnPreUpdate*/ }
}

(this is completely untested and might fail with values retrieved from the DB. Use as a starting poing)

score 1 · Answer 2 · answered Jun 03 '11 at 18:07

Putting aside the argument of whether dates should be stored in UTC or not, the place to check for these kinds of things in your code is in the build process, or failing that, using a pre-commit hook.

The latter will only allow the developer to commit code that adheres to your standards. Doing it at the database layer is too late, IMO.

score 0 · Answer 3 · answered Jun 03 '11 at 18:03

You could write a trigger in your database that logs (or does something else) when a DateTime that you expect to be around Now isn't anywhere near UTC now.

You could make your EF or NHibernate layer default date fields to UTC Now so developers don't need to set them.

Probably better, you could just search the code for DateTime.Now and review usages.

score 0 · Answer 4 · answered Jun 03 '11 at 18:03

I think in NHibernate you can use interceptor class to override base methods like OnLoad on OnSave. You can try to intercept property type and manage there your date before insert in db.

look here:

http://knol.google.com/k/fabio-maulo/nhibernate-chapter-11-interceptors-and/1nr4enxv3dpeq/14#

score 0 · Answer 5 · answered Jun 03 '11 at 18:05

The key to dealing with bad data is to eliminate it at the earliest possible time. By the time you've gotten to the database layer, it's way too late to do anything useful.

It's probably impossible to detect the use of DateTime.Now in SQL queries at runtime. You're better off going through your code and eliminating all uses of DateTime.Now. Everywhere. Set a coding standard so that all dates and times throughout your program are represented in UTC, except when they have to be output in local time or some other timezone. That will be a much better long-term solution to the problem.

Impossible? We don't use that word around here. – Diego Mijelshon Jun 05 '11 at 02:24 — Diego Mijelshon, Jun 05 '11 at 02:24

score 0 · Answer 6 · answered Jun 05 '11 at 15:00

You shouldnt be introducing code to enforce a rule on your developers coding style. You are unnecessarily introducing overhead in your app. There are ways you can address this:

a) Have regular code reviews and make sure all code checked in is verified

b) The other way is to enforce this in your data model. Why dont you use a DateTimeOffset property on this field and stop worrying.

Incercept when DateTime.Now is used when writing to a Database

6 Answers6