3

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.

Improve this question

1 Answer 1

Reset to default
3

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

Improve this answer
Sign up to request clarification or add additional context in comments.

23 Comments

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).
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).
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.
@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?
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): stackoverflow.com/questions/4331189/datetime-vs-datetimeoffset
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.