By Zack Jannsen

2013-03-27 23:44:55 8 Comments

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.


@Aaron Bertrand 2013-03-27 23:53:51

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

@Zack Jannsen 2013-03-28 00:01:31

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.

@Aaron Bertrand 2013-03-28 00:05:41

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

@Zack Jannsen 2013-03-28 00:06:15

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

@Zack Jannsen 2013-03-28 00:06:45

Yes. Timezone ware. Sorry.

@Aaron Bertrand 2013-03-28 00:07:15

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

@Zack Jannsen 2013-03-28 00:44:36

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

@Zack Jannsen 2013-03-28 00:51:09

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

@Aaron Bertrand 2013-03-28 00:51:12

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.

@Aaron Bertrand 2013-03-28 00:56:09

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 Jannsen 2013-03-28 00:59:21

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?

@Aaron Bertrand 2013-03-28 01:01:24

@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?

@Zack Jannsen 2013-03-28 11:26:52

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

@Zack Jannsen 2013-03-28 11:29:33

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

@Zack Jannsen 2013-03-28 14:17:09

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?

@Aaron Bertrand 2013-03-28 14:18:48

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

@Zack Jannsen 2013-03-28 23:39:08

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.

@Aaron Bertrand 2013-03-28 23:47:10

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

@Aaron Bertrand 2013-03-28 23:53:03

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

@Zack Jannsen 2013-03-29 00:18:43

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's TimeZoneInfo type?

@Aaron Bertrand 2013-03-29 00:20:28

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

@Zack Jannsen 2013-03-29 00:55:34

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):‌​et

@Zack Jannsen 2013-03-29 01:10:36

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.

@Aaron Bertrand 2013-03-29 01:10:56

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

Related Questions

Sponsored Content

12 Answered Questions

[SOLVED] How to make an unaware datetime timezone aware in python

13 Answered Questions

[SOLVED] Convert UTC datetime string to local datetime

13 Answered Questions

[SOLVED] Set NOW() as Default Value for datetime datatype?

25 Answered Questions

[SOLVED] How do you set a default value for a MySQL Datetime column?

  • 2008-10-03 20:24:30
  • Brian Boatright
  • 1309375 View
  • 913 Score
  • 25 Answer
  • Tags:   mysql datetime

1 Answered Questions

[SOLVED] Event Relative Time

  • 2016-10-30 15:25:21
  • DanTheDJ1
  • 60 View
  • 1 Score
  • 1 Answer
  • Tags:   php mysql datetime

1 Answered Questions

3 Answered Questions

[SOLVED] What is the difference between tier vs layer application?

1 Answered Questions

[SOLVED] confusion around DateTimeOffset and TimeZones

Sponsored Content