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.

Woodard

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.)

Napkin

The DATETIMEOFFSET value is UTC aware. DATETIME is not. That is why I am considering this. If the database is split across many servers, or moves to a new time zone I want to avoid errors due to datetime types that are unaware of universal time. – Woodard

I don't understand. If you populate a DATETIME value with SYSUTCDATETIME(), why do you think it needs to be "UTC aware"? Are you confusing "UTC aware" with "timezone aware"? UTC is UTC no matter where your servers are or what time zone they're in, and if you insert SYSUTCDATETIME() into a DATETIME column, guess what? That's UTC. You would only use DATETIMEOFFSET if you need to retain information about the source time zone, which is why I added the comment (because it doesn't sound like you need to do this). – Napkin

I am leaning to the insert and sproc as that is already built in ... just making sure before I keep coding this way. – Woodard

Yes. Timezone ware. Sorry. – Woodard

Ok so can you explain further how you're going to use the time zone information if you are storing the data in UTC? Just saying "well it's timezone-aware!" doesn't explain to me how you're actually going to use it and what benefit you're going to get by using DATETIMEOFFSET. I mean, do what you want, obviously, I just question what you'll actually gain in this case (for potentially a lot of unnecessary hassle). – Napkin

I am curious, you mention "a lot of unnecessary hassle". Can you elaborate? – Woodard

I would opt for DATETIME2 as it is the recommended choice on msdn documentation going forward if I do not keep DATETIMEOFFSET. – Woodard

Well, for one, there is no DST awareness built in. So if you store a value in UTC in July, and another one in December, and want to switch to, say, Eastern time, how are you going to do it? In addition, if you have data that spans back more than a couple of years, the rules for when DST occurs in the US changed, so your rules have to be even more complex. – Napkin

That is not a bad idea, however if you don't need the full granularity of DATETIME2(7) be sure to specify the precision explicitly. – Napkin

On precision - always. I agree. Good point on DST. Sticking with UTC functions to set date and a offset unaware data type ... I would then use javascript on the front end to grab the user's "offset" and / or have them tell me and keep the offset in the database. Does this sound like a simpler plan of action? – Woodard

@Zack no. JavaScript can tell you what the end user's offset is right now, but what if you are showing them a value from December, when the offset might have been different? What if you are returning a set of data that traverses multiple DST changes? What if they are saving a value from December? – Napkin

Do you know if WCF has fixed the requirement to send all dates in "local" server time? Storing dates in SQL as UTC in SQL, created in SQL solves a lot of applicaiton problems, but I want to make sure once I serialize them they remain UTC by the time they hit the presentation layers. Otherwise I will need to convert these dates to server time before sending OR to a string (which makes sense as they will be converted anyway). If going the string route would you want the serialzied data to carry the zero offset info with it? Futureproofing is the goal (if one day I offer an API). – Woodard

I should add - I am using ASP.NET 4.5 on all layers at this point and NO legacy code is considered. – Woodard

After thinking and reading more on this: I like the idea of "unambiguity" that DATETIMEOFFSET holds. It states that the date is UTC 0:00 and only costs 2 byts more at 2 precision. In the applicaiton, using DateTimeOffset type and TimeZoneInfo class I should be able to convert too and from the UTC times correctly. I will need the user to define their timezone. That is a must I suppose. WCF does not provide a datacontract on the .net DateTimeOffset class by default so I would need to be explicit. Other than extra space, using these technologies together should get me 99% there wouldn't it? – Woodard

@Zack sorry but I'm at a loss as to where "there" is... shrug – Napkin

by "there' I mean having a solution that can equate to each users local time on the browser end but remain universal on the back end and honor time. DLS is the big challenge as you stated ... I' will be giving you credit for an answer, but if you have any insight into elegant ways to handle DLS I am all ears. – Woodard

@Zack At my previous job I handled DST using a calendar table. For every supported time zone, there was a row for every day say from 20000101 -> 20301231. The table had three columns: date, timezoneID, and offset (in minutes). This way the table could be updated at any point to reflect the proper transition between DST, and the offset could be triangulated using the date and the timezone based on the user (we used user preferences but you could use whatever the browser tells you). – Napkin

@Zack oh and about WCF, I don't even know that I know how to spell that, sorry. If you are still interested in passing the adjusted values around, instead of just dealing with UTC until presentation time, you should ask a WCF-focused question. – Napkin

I think I will opt for UTC until presentation thank you very much. Great advice on that. There are a few issues I have seen out there in docs, but most are pre .net 3.5. AS for the translation for DLS would I still need the backing table with the advances in asp.net's TimeZoneInfo type? – Woodard

@Zack sorry, don't know. I get to stay away from .NET development most of the time and I don't know if it's any better. I know there have been some limitations and omissions but I'm not sure if they have all been fixed. I'm a DBA and so my instinct is to put data into a table, therefore I can control it. The table is tiny, and if it's referenced enough it's always in memory, so if you're concerned about performance, don't be. – Napkin

Well from a DBA perspective great answer Aaron. From what I am reading on the middletier side of things TimeZoneInfo seems to be the way to go and it sounds like it has most of the timezone info accounted for. Here is a good answer to a similar question (see second answer): https://mcmap.net/q/25756/-datetime-vs-datetimeoffset – Woodard

Final Point: Per C# 4 In a Nutshell: "The TimeZone and TimeZoneInfo classes proved information on timezone names, UTC offsets, and daylight saving time rules". If we are not dealing with USA 1944-65 where things seem to be a bit wacky then the class should handle the conversion in the applicaiton layer when used correctly. All DST rules are included in the classes. – Woodard

@Zack yeah lack of inherent DST support is a real downer for DATETIEMOFFSET. – Napkin