sql server - Where to set a UTC datetime value in n-tier application: Presentation Layer, Domain, or Database?

Question

This seems like it should be an obvious question, but I have had some issues finding a good answer. I am building an n-tier application that needs to be UTC time sensitive. Values can be updated and when they are timestamps are recorded. This includes transactions in the database where updates or inserts will impact datetime columns.

To give some context I am using SQL 2008 R2 + with DATETIMEOFFSET(2) for most of my datetime columns. I am considering putting the updates for timestamps into the Stored Procedures so that they do not need to be passed through the network. This will save bandwidth as the system grows which is a good thing ... and can be used to validate if data changes (first wins) on shared data. The down side is that the first to submit a transaction may not be the one who wins if they run into slower response time on their instance of the application.

What is the ideal or recommended way to handle UTC time data in this context?

1. Set it in the SPROC with SYSUTCDATETIME() OR ...
2. Set it in the application with DateTimeOffset.Now or DateTime.UtcNow

If two above, would it be recommended to fire this at the Presentation Layer and pass it through the service to the domain layer or just set it when it hits the domain on the back end of the service?

As you can see there are a lot of options here and I am leaning toward the database ... but I would appreciate any advice or words of warning before I continue building this thing out.

Side note: I am tracking geospatial info as well ... but this is not a hard real-time system. User real time is more than adequate.

UPDATE: I will be using DateTimeOffset in the applicaiton. My research has lead me to uncover that you "can reliably compare any tow DateTimes by first calling ToUniversalTime on each. This strategy fails if (and only if) exactly one of them has a DatTimeKind of "Unspecified".his potential for failure is another reason for favoring DateTimeOffset" - C# 4.0 In a Nutshell, O'Riely books.

Answer 1

I vote for setting it in the procedure (or the default for the column, for inserts). There is no reason to pass all of this information through all the layers unless you need microsecond accuracy to differentiate e.g. when the user clicked the button vs. when the transaction was committed in the database. This is especially true if you have a distributed application - do you want to rely on all of your web/application servers to be in sync, never mind end user workstations for client/server apps? You may have servers in different data centers, all with different time zones, some observing DST, some not, etc. DateTime.UtcNow should obliterate most of those differences but I'd still go back to passing all that data around for no reason. The database knows what time it is; let it store the value for you and keep all that logic out of the application.

(Also if you are storing UTC time, do you really need DATETIMEOFFSET? If so, then you still need some way for the procedure to know which time zone this information came from. If not, then you should probably just use SMALLDATETIME/DATETIME/DATETIME2 depending on accuracy required.)