You Know What They Say About The Man Who Wears Two Watches...

... He never knows the correct time. I just heard this saying for the first time earlier this week, and today it definitely rang true for me.

The application I'm working on right now has some pretty standard audit/logging tables which store changes to a record over time. The table looks something like:

,ObjectID INT
,ChangeDate DATETIME
,FieldName VARCHAR (100)
,OldValue VARCHAR (255)
,NewValue VARCHAR (255)

When a field is changed on a record the update gets logged to this table. So if I updated the LastSaved field of ObjectID 123 the change might get logged to the table like so:

UserID ObjectID ChangeDate FieldName OldValue NewValue
1 123 {ts '2007-11-02 15:57:34'} LastSaved 2007-11-01 12:33:12 2007-11-02 15:57:34

In the above example you will see that the ChangeDate and NewValue are the same. This is the way the application should work, however today I ran into an issue where the log was looking something like this:

UserID ObjectID ChangeDate FieldName OldValue NewValue
1 123 {ts '2007-11-02 15:57:34'} LastSaved 2007-11-01 12:33:12 2007-11-02 16:57:10

Notice the ChangeDate and NewValue are off by almost an hour. Well it turns out this table was setting the fields from two separate sources. The ChangeDate was being set at the database with SQL Server's GetDate() function while the NewValue was being set from ColdFusion with the Now() function. Now, I've posted about Daylight Saving Time issues in the past but it seems I can't get away from these issues. Apparently the time server our ColdFusion servers syncs to was not correctly patched for this years DST changes so it was reporting the time it sent out as Eastern Standard Time. Our ColdFusion servers, which are correctly patched, show that we are still in Eastern Daylight Time so they were adding an hour to the time being sent out by our time servers.

Outside of the DST issue, this got me thinking. When logging times that you plan to compare it is probably best to get those times from the same source. Even if my ColdFusion server had the correct time the the ChangeDate and LastSaved NewValue could be off by a few seconds just due to differences in the times on the two servers. (The database server and the ColdFusion server.) Anyway, just something I thought I'd share that you may want to watch out for.

Michael Wolfe's Gravatar More than a year later and they still haven't fixed that problem?!?!
# Posted By Michael Wolfe | 11/5/07 3:56 PM
BlogCFC was created by Raymond Camden. This blog is running version 5.8.001.