如何在SQL中处理时间存储

I have a web application that I'm writing (C#, MSSQL) and I need to store the timestamp when a record is stored in the system. Normally, I would just do this with SQL and the DATETIME function. However, the server is located in a different time zone than our company is located... AND we may change to another server in completely different time zone. The hosting company will not change the server time to match our local time zone. (Not that I blame them, but it was one thing I tried.)

我有一个我正在写的Web应用程序（C＃，MSSQL），我需要存储记录存储在系统中的时间戳。通常，我会使用SQL和DATETIME函数执行此操作。但是，服务器位于与我们公司所在的时区不同的时区...我们可能会在完全不同的时区更改为另一台服务器。托管公司不会更改服务器时间以匹配我们的本地时区。（不是我责备他们，但这是我尝试过的一件事。）

So, my question is, what is the best way to store the date/time of the record update and what is the best way to present that date/time back to the user in our local time zone?

所以，我的问题是，存储记录更新的日期/时间的最佳方法是什么，以及在当地时区将该日期/时间呈现给用户的最佳方式是什么？

I want the least messy way of doing this, so a combo of C# and SQL would be fine as long as the solution is easy to implement. (My normal style is to do more work in stored procedures and less in C#, if that matters.)

我想要这样做最简单的方法，所以只要解决方案易于实现，C＃和SQL的组合就可以了。（我的正常风格是在存储过程中做更多的工作而在C＃中做更少的工作，如果这很重要的话。）

Can you show me some sample code? Thanks!

你能告诉我一些示例代码吗？谢谢！

3 个解决方案

#1

always store DATETIME data in Universal Time Coordinated (UTC aka GMT)

始终以协调的世界时（UTC aka GMT）存储DATETIME数据

this avoids all timezone issues
这可以避免所有时区问题
this avoids daylight-savings-time issues
这避免了夏令时问题
this allows simple date math to always work
这允许简单的日期数学始终有效
this allows transactions in different time zones to stay in synch
这允许不同时区的交易保持同步
this allows you to move the data to a different server in a different time zone and not screw everything up
这允许您将数据移动到不同时区的不同服务器，而不是将所有内容都搞砸
etc. "Universal" time, make sense?
等“通用”时间，有意义吗？

C# DateTime provides DateTime.UtcNow and ToLocalTime(), SQL provides GETUTCDATE(). Here's a SQL function to convert UTC to local time -

C＃DateTime提供DateTime.UtcNow和ToLocalTime（），SQL提供GETUTCDATE（）。这是一个将UTC转换为本地时间的SQL函数 -

-- convert UTC to local time
create FUNCTION [dbo].[udfUtcToLocalTime]
(
    @gmt datetime
)
RETURNS datetime
AS
BEGIN
    DECLARE @dt datetime
    SELECT 
        @dt = dateadd(millisecond,datediff(millisecond,getutcdate(), getdate()),@gmt)
    RETURN @dt
END

example:

例：

SELECT dbo.udfUtcToLocalTime(someDateTimeField)
FROM someTable

#2

Save UTC times in your database and localize those times every time you wanna show them to a user

在您的数据库中保存UTC时间，并在每次向用户显示时将这些时间本地化

#3

Store it as UTC time and then preform the time zone calculation on the client end in C# use .ToUniversalTime() on the DateTime object to get the UTC time, store that on the server and then use .ToLocalTime() to convert it back.

将其存储为UTC时间，然后在客户端上对C＃使用.ToUniversalTime（）在DateTime对象上执行时区计算以获取UTC时间，将其存储在服务器上，然后使用.ToLocalTime（）将其转换回来。

#1