在n层应用程序中设置UTC datetime值的位置：表示层，域或数据库？ [英] Where to set a UTC datetime value in n-tier application: Presentation Layer, Domain, or Database?

问题描述

这似乎应该是一个明显的问题，但是我有一些问题找到了一个很好的答案。我正在构建一个需要UTC时间敏感的n层应用程序。可以更新值，并记录它们的时间戳。这包括数据库中的更新或插入将影响datetime列的事务。

为了给出一些上下文，我正在使用SQL 2008 R2 +与DATETIMEOFFSET（2） datetime列。我正在考虑将时间戳的更新放入存储过程，以便不需要通过网络传递。这将节省带宽，因为系统增长是一件好事...并且可以用于验证数据是否在共享数据上更改（首先获胜）。不利的是，首先提交交易可能不是在他们的应用程序实例中遇到较慢响应时间的人。

什么是理想或推荐的方式来处理这个上下文中的UTC时间数据？

1. 使用SYSUTCDATETIME（）OR ...将其设置在SPROC中


2. 将其设置为应用程序与DateTimeOffset.Now或DateTime.UtcNow

如果两个以上，是否建议在演示层触发它并传递通过服务到域层，或者在服务的后端点击域时设置它？

正如你所看到的，这里有很多选项我正在倾向于数据库...但是，在继续构建这个事情之前，我会感谢任何建议或警告词。

旁注：我正在跟踪地理空间信息也是...但这不是一个硬实时系统。用户实时是足够的。

更新：我将在应用中使用DateTimeOffset。我的研究使我发现，可以通过首先在每个日期调用ToUniversalTime来可靠地比较任何拖尾DateTimes。如果（并且只有）其中一个具有未指定的DatTimeKind，则该策略才会失败。原因有利于DateTimeOffset - C＃4.0在一个Nutshell，O'Riely的书。

解决方案

我投票设置它在程序（或列的默认值，用于插入）。没有理由通过所有层传递所有这些信息，除非您需要微秒的精度来区分例如。当用户点击了按钮，而在数据库中提交了事务。如果您有分布式应用程序，那么您是否希望依靠所有Web /应用程序服务器进行同步，尤其是在客户端/服务器应用程序的最终用户工作站中？您可能在不同数据中心的服务器，都有不同的时区，一些观察DST，一些不等等。 DateTime.UtcNow 应该消除大部分差异，但我会仍然没有理由回传所有的数据。数据库知道什么时候是让它存储你的价值，并保持所有的逻辑从应用程序。

（另外如果你正在存储UTC时间，你真的需要 DATETIMEOFFSET ？如果是这样，那么你仍然需要一些方法来知道这个信息来自哪个时区，如果没有，那么你应该可以使用 SMALLDATETIME / DATETIME / DATETIME2 取决于所需的准确度。）

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.

解决方案

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

这篇关于在n层应用程序中设置UTC datetime值的位置：表示层，域或数据库？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！