Asp.Net，SQL和TimeZones [英] Asp.Net, SQL and TimeZones

问题描述

有人问过，但是我正在努力掌握如何在Web应用程序中处理时区的概念.我有一个跟踪项目进度的系统.我的SQL Server数据库中有一个ProjectStartDate DATE.(还有更多的字段和表，但让我们集中讨论一个.)

It's been asked, but I am battling to grasp the concept of how to handle timezones in a web app. I have a system that tracks the progress of projects. I have a ProjectStartDate DATE in my SQL Server database. (There's a few more fields and tables, but lets focus on one).

服务器位于美国的某个地方.我住在澳大利亚.

The server is located somewhere in the United States. I live in Australia.

调用 SELECT GETDATE()会返回"2013-08-11 14:40:50.630"我的系统时钟显示为"2013-08-12 07:40"

Calling SELECT GETDATE() returns "2013-08-11 14:40:50.630" My system clock shows "2013-08-12 07:40"

在我的数据库中，所有表上都有"CreateDateTime"列.当我将其存储在c#代码中时，我使用 CreateDate = DateTime.UtcNow

In my database, I have 'CreateDateTime' columns on all tables. When I store that, within my c# code, I use CreateDate = DateTime.UtcNow

我使用它，因为我听说使用UTC更好.

I use that, as I heard it was better to use UTC.

但是，当向用户显示日历控件，并且他们为项目选择了开始日期"时，我将存储用户选择的内容.没有转换...就像我说的，StartDate是数据库中的DATE类型.

But, when a user is presented with a calendar control, and they select a Start Date for a project, I store what ever the user selected. No conversion... And as I said, the StartDate is a DATE type in the database.

问题是，如果一个项目今天开始-我的前端说当前项目是没有开始"，因为服务器仍在昨天.

The problem is, if a project started today - my front end says that the current project is No Started, because the server is still in Yesterday.

我认为日期应该在存储时存储.但是也许我需要以某种方式获取用户的时区，并将其应用于UI级别?

I think the dates should be stored as I am storing them. But maybe I need to somehow get the users timezone, and apply that on the UI level?

我看到的问题是:

• 我不知道用户的时区.添加一些内容以允许他们选择吗?
• 项目状态可能在存储过程中确定，那么什么时候可以应用转换?在proc中，如果StartDate< = DateTime.Now?
，它可能会进行检查，并返回一个VARCHAR，指出未启动".

• I don't know the users timezone. Add something to allow them to select it?
• The status of the project maybe determined in a stored procedure, so when can I apply the conversion? In the proc, it might do a check, and return a VARCHAR stating "Not Started" if the StartDate <= DateTime.Now?

我大多数时候使用EntityFramework和Linq来获取数据.我需要一种从SQL和.Net两种角度插入和检索数据的策略.

I use EntityFramework and Linq to get data most of the time. I need a strategy for inserting and retrieving data, both from a SQL sense, and a .Net sense.

我添加了代码，以使用户根据此选择时区:

I have added code to get the user to select their timezone based on this:

public List<TimeZoneDto> GetTimeZones()
{
    var zones = TimeZoneInfo.GetSystemTimeZones();
    var result = zones.Select(tz => new TimeZoneDto
        {
            Id = tz.Id, 
            Description = tz.DisplayName
        }).ToList();

    return result;
}

然后将其保留在他们的个人资料中.

That is then persisted in their profile.

所有日期都存储为UTC，如下面的答案所建议.

All dates are being stored as UTC, as advised in the answers below.

我仍然很困惑如何处理从数据库到客户端的日期.这是我如何存储记录的示例:

I'm still confused how to handle the dates when they go to and from the database, to the client. Here is an example of how I am storing a record:

public int SaveNonAvailibility(PersonNonAvailibilityDto n)
{
    person_non_availibility o;

    if (n.Id == 0)
    {
        o = new person_non_availibility
            {
                PersonId = n.PersonId,
                Reason = n.Reason,
                StartDate = n.StartDate,
                EndDate = n.EndDate,
                NonAvailibilityTypeId = n.NonAvailibilityTypeId,
                CreateUser = _userId,
                CreateDate = DateTime.UtcNow

            };
        _context.person_non_availibility.Add(o);
    }
    else
    {
        o = (from c in _context.person_non_availibility where c.Id == n.Id select c).FirstOrDefault();
        o.StartDate = n.StartDate;
        o.EndDate = n.EndDate;
        o.Reason = n.Reason;
        o.NonAvailibilityTypeId = n.NonAvailibilityTypeId;
        o.LastUpdateDate = DateTime.UtcNow;
        o.LastUpdateUser = _userId;
        o.Deleted = n.Deleted ? DateTime.UtcNow : (DateTime?)null;
    }
    _context.SaveChanges();
    return o.Id;
}

基本上，当一个人不能上班时，此方法可以节省.请注意我存储LastUpdateDate的方式.同样，开始"和结束"日期.这些日期更像是营业"日期.

This method basically saves when a person is not available for work. Note the way I store the LastUpdateDate. Also, the 'Start' and 'End' dates. Those dates are more 'Business' dates.

在选择时，然后进行日期检查是我遇到的问题.在此示例中，我将根据NOW得出人员收费率.

On selection, and then date checking, is where I have issues. In this example, I am getting a persons rate of charge, based on NOW.

public decimal CurrentRate
{
    get
    {
        if (ResourceCosts != null)
        {
            var i = ResourceCosts.FirstOrDefault(t => DateTime.UtcNow <= (t.EndDate.HasValue ? t.EndDate.Value : DateTime.UtcNow) && DateTime.UtcNow >= t.StartDate);
            if (i != null) return i.Cost;
            return 0;
        }
        return 0;
    }
}

因此，我要在此执行的操作是，基于当前日期，我要查看他的费率(因为从1月1日到1月15日，他的费率可能是100美元，而从1月15日到1月15日是110美元1月16日至1月31日.因此，我寻找今天适用的汇率(如果有的话).跨时区无法使用，也许在这里我需要根据'DateTime.UTCNow'?

So, what I am wanting to do here, is, based on the current date, I want to see his rate (As his charge our rate maybe be $100 from the 1st Jan, to the 15th Jan, and then $110 from the 16th until the 31st of Jan. So, I look for the rate applicable today (if any). This doesn't work across time zones, and it's maybe here where I need to do some date manipulation based on the 'DateTime.UTCNow'?

注意，我现在根据上面存储他的时区的代码知道用户的时区.我可以在这里使用它吗?也许，当用户登录时，从其个人资料中获取日期信息(Zimezone信息)，然后具有全局共享功能，该功能基于在UTC日期中添加或删除小时数来返回用户的日期时间...我在哪里做DateTime.UTCNow?

Note, I now know the users timezone based on the code above where I am storing his timezone. I can use that somehow here? Maybe, when the user logs in, grab the date info from his profile (Zimezone info), and then have a global shared function, that returns the users datetime, based on adding or removing hours from the UTC Date... and using that where ever I am doing DateTime.UTCNow?

希望有人可以引导我.

推荐答案

您可能会发现，没有一种单一的正确"方式来处理所有这些问题.对于您在问题中描述的几个不同问题，有多种解决方法.我将尝试澄清几点.

You may find that there is not one single "right" way to handle all of this. There are multiple approaches to the several different problems you describe in your question. I will attempt to clarify a few points.

• 首先，永远不要尝试考虑服务器上的本地时间.您的代码和数据不必根据部署位置进行更改.您说您的服务器在美国，但是要考虑多个时区，并且许多服务器的时区都将设置为UTC，而不论其物理位置如何.

• First, don't ever attempt to think about local time on a server. Your code and data should not have to change based on where you deploy it. You said your server was in the USA, but there are multiple time zones to consider, and many servers will have their time zone set to UTC regardless of their physical location.

在SQL Server中，应避免使用 GETDATE()或 SYSDATETIME().如果您需要使用SQL的当前时间戳，请使用 GETUTCDATE() 或 SYSUTCDATETIME() .如果由于某种原因服务器的时区对您很重要，请使用 SYSDATETIMEOFFSET() .

You should avoid GETDATE() or SYSDATETIME() in SQL Server. If you need a current timestamp in SQL, use GETUTCDATE() or SYSUTCDATETIME(). If for some reason the server's time zone is important to you, use SYSDATETIMEOFFSET() instead.

同样，避免从任何服务器端代码中使用.Net中的 DateTime.Now .使用 DateTime.UtcNow 或 DateTimeOffset.UtcNow 作为UTC时间戳，或使用 DateTimeOffset.现在 ，如果由于某种原因服务器的时区对您很重要.

Likewise, avoid using DateTime.Now in .Net from any server-side code. Use DateTime.UtcNow or DateTimeOffset.UtcNow for a UTC timestamp, or use DateTimeOffset.Now if for some reason the server's time zone is important to you.

您可以在我的博客文章中了解更多相关信息:反对DateTime.Now

You can read more about this in my blog post: The Case Against DateTime.Now

接下来，让我们谈谈您正在使用的数据类型.SQL Server中的 date 类型仅存储日期.就是这样.没有时间，没有偏移，也没有时区.例如 2013-08-11 .当您确实要输入整个日历日期时，应该使用它.全球没有今天"的统一语境.相反，每个人都基于各自的时区具有自己的含义.另外，并非每个日历日都是24小时.一天可能长达23、23.5、24、24.5或25小时，具体取决于在特定时区中应用夏时制的时间，以及是否要评估DST转换的日期.

Next, let's talk about the data type you're using. The date type in SQL Server stores just a date. That's it. No time, no offset, and no time zone. An example would be 2013-08-11. You should use it when you really mean a whole calendar date. There is no worldwide uniform context of "today". Instead, everyone has their own meaning based on their time zone. Also, not every calendar day is 24 hours in length. A day could be 23, 23.5, 24, 24.5 or 25 hours long, depending on how daylight saving time is applied in the particular time zone, and if you are evaluating the day of a DST transition.

.Net中-没有 Date 类型，因此将SQL date 转换为 DateTime ，并将时间设置为午夜( 00:00:00 )，并且类型设置为 Unspecified .但是请不要自欺欺人-时间不会突然在午夜，我们只是在填零时间.这会导致很多错误和混乱.(如果要避免这种情况，可以尝试使用 LocalDate 类型的 Noda Time 为此目的.)

In .Net - there is no Date type, so a SQL date is converted to a DateTime with the time set to midnight (00:00:00), and the kind set to Unspecified. But don't fool yourself - the time isn't suddenly midnight, we are just filling in zeros for the time. This can lead to a lot of error and confusion. (If you want to avoid that, you can try Noda Time, which has a LocalDate type for this purpose.)

您真正需要考虑的问题是什么:

What you really need to be thinking about, and haven't defined in your question, is this:

一个项目在什么时间开始?

现在您只是在说 2013-08-11 ，它不是指特定的时间.您是指某个特定时间段的一天的开始吗?还是您说的是根据用户的时区的一天的开始?那些可能不是同一回事.除非您知道正在谈论什么时间，否则您无法与任何人的现在"(utc，本地或其他)进行比较.

Right now you are just saying 2013-08-11, which doesn't refer to a specific moment in time. Do you mean the beginning of that day in a particular time zone? Or do you mean the beginning of that day according to the user's time zone? Those might not be the same thing. You can't compare to anyone's "now" (utc, local, or otherwise) unless you know what moment in time you are talking about.

如果项目在全球某个确切的时间开始，那么最简单的方法是存储一个 datetime (或 datetime2 )类型，其中包含该精确时间世界标准时间.因此，您可能会说一个项目开始于 2013-08-10T14:00:00Z -恰好是8月11日午夜，澳大利亚悉尼.在.Net中，您可以使用 DateTime 类型，将 .Kind 设置为 Utc .

If the project starts at an exact moment in time worldwide, then the easiest thing would be to store a datetime (or datetime2) type that contains that precise time in UTC. So you might say that a project starts at 2013-08-10T14:00:00Z - which would be exactly midnight on August 11th in Sydney, Australia. In .Net, you would use a DateTime type with the .Kind set to Utc.

另一种表示方式是通过存储值 2013-08-11T00:00:00 + 10:00 的 datetimeoffset 类型-在同一时间点，但使用偏移量为您提供了一个预先转换的值.(悉尼当天的UTC + 10).您将使用 DateTimeOffset 类型在.Net中使用此类型.

Another way you could represent this is by storing a datetimeoffset type that has a value of 2013-08-11T00:00:00+10:00 - which is the same moment in time, but uses the offset to give you a value that is pre-converted. (Sydney is at UTC+10 on that date). You would use the DateTimeOffset type to work with this in .Net.

但是，如果项目根据用户的不同时间启动，那么它实际上并不是一个确切的时刻.这更像是一个浮动"的开始.如果将来自世界各地的用户分配到同一项目，则某些用户可能先于其他用户开始.如果您打算这样做，那么如果所有项目都在午夜开始，则可以使用 date 类型，也可以使用 datetime 或( datetime2 )，如果项目可能在不同的时间开始.在您的.Net代码中，您将使用 DateTime 类型，将 .Kind 设置为 Unspecified .

But if the project starts at different times depending on the user, then it's not really an exact moment in time. It's more of a "floating" start. If users from different places around the world are assigned to the same project, then some users could be starting before others. If that's your intention, then you can just use the date type if all projects start at midnight, or you can use a datetime or (datetime2) type if projects might start at different times. In your .Net code, you would use a DateTime type with the .Kind set to Unspecified.

关于获取用户的时区，您最好的办法就是询问他们.尽管存在常见的误解-您不能仅从浏览器中获取它.您可以从浏览器中得知的是它们的 current 偏移量是多少.(请阅读时区标签Wiki 的"TimeZone！=偏移"部分.)

With regard to getting the user's time zone, the best thing you could do would be to ask them. Despite the common misconception - you can't just get it from the browser. All you could tell from the browser is what their current offset is. (Read the "TimeZone != Offset" section of the timezone tag wiki).

在询问用户其时区时，如果您决定使用Windows时区，则可以从 TimeZoneInfo.GetSystemTimeZones 方法. .Id 是存储在数据库中的密钥，并且向用户显示 .DisplayName .以后，您可以使用 TimeZoneInfo.FindSystemTimeZoneById 方法来获取 TimeZoneInfo 可用于转换的对象.

When asking the user for their time zone, if you decide to use Windows time zones you can produce a dropdown list from the TimeZoneInfo.GetSystemTimeZones method. The .Id is the key you store in your database, and you show the .DisplayName to the user. Later you can use the TimeZoneInfo.FindSystemTimeZoneById method to get a TimeZoneInfo object that you can use for conversions.

如果您想更精确一些，可以使用IANA时区而不是Windows时区.为此，我建议使用基于地图的时区选择器控件，例如此.您也可以使用 jsTimeZoneDetect 来猜测控件的默认值.在服务器上，您可以使用 Noda Time 进行时区转换.

If you wanted to be more precise, you could use IANA time zones instead of the Windows time zones. For that, I recommend using a map-based timezone picker control, such as this one. You might also use jsTimeZoneDetect to guess a default value for your control. On the server you would use Noda Time to perform time zone conversions.

有一种不需要时区转换的方法.基本上，您会在UTC中进行所有操作.这包括将时间传输到UTC中的浏览器.然后，您可以使用JavaScript来获取用户的当前时间 UTC时间并与之进行比较.

There is an approach that doesn't require time zone conversions. Basically, you do everything in UTC. That includes transmitting the time to the browser in UTC. You can then use JavaScript to get the user's current time in UTC and compare against that.

如果愿意，可以使用JavaScript Date 类的各种功能来执行此操作.但是您可能会发现使用 moment.js 之类的库更容易.

You can use various functions of the JavaScript Date class to do this if you wish. But you may find it easier to work with a library such as moment.js.

尽管这种方法对很多事情都可行，但安全性并不是其中之一.您的用户可以轻松更改计算机时钟来解决此问题.

While this approach is viable for many things, security is not one of them. Your user can easily change the clock of their computer to work around this.

另一种方法是将服务器端与UTC进行比较.如果您的数据库中有确切的UTC开始时间，则只需检查.Net代码中的 DateTime.UtcNow ，然后使用它来决定要执行的操作.您不需要用户的时区进行比较，但是如果您想向他们显示本地时间意味着什么，就将需要它.

Another approach would be to compare server-side against UTC. If you have the exact UTC starting time in your database, then you can just check DateTime.UtcNow in your .Net code and use that to decide what to do. You won't need the user's time zone to make this comparison, but you will need it if you want to show them what that means in their local time.

我希望这可以消除混乱，并且不会使情况变得更糟！:)如果您还有其他问题，请编辑您的问题或在评论中提问.

I hope this clears up the confusion and didn't make it worse! :) If you have additional concerns, please edit your question or ask in comments.

更新

针对您更新的问题，建议您尝试以下操作:

In response to your updated question, I suggest you try the following:

var timeZoneId = "Eastern Standard Time"; // from your user's selection
var timeZone = TimeZoneInfo.FindSystemTimeZoneById(timeZoneId);
var nowInTimeZone = TimeZoneInfo.ConvertTimeFromUtc(DateTime.UtcNow, timeZone);
var todayInTimeZone = nowInTimeZone.Date;

var i = ResourceCosts.FirstOrDefault(t => t.StartDate <= todayInTimeZone &&
            (!t.EndDate.HasValue || t.EndDate >= todayInTimeZone));

当然，这意味着在您的 StartDate 和 EndDate 字段中，您不会将它们存储为UTC，而是存储为与用户.当您应用时区时，它们仅与特定时间对齐，因此相同的UTC时间戳记可能会落在不同的日期，具体取决于用户所在的时区.

Of course this means that in your StartDate and EndDate fields, you are not storing these as UTC - but rather as the "business dates" that are relevant to the user. These only line up to a specific moment in time when you apply a time zone, so the same UTC timestamp could fall on different dates depending on what the user's time zone is.

此外，您使用的是完全包含范围，对于这些种类的日历日期范围，通常是可以的.但是请确保您意识到可能存在重叠.因此，如果您有 2013-01-01-2013-02-01 和 2013-02-01-2013-03-01 ，那么有一天是> 2013-02-01 都在两个范围内.

Also, you are using fully inclusive ranges, which is usually OK for these kind calendar date ranges. But make sure you realize that there could be overlap. So if you have 2013-01-01 - 2013-02-01 and 2013-02-01 - 2013-03-01, then there is that one day 2013-02-01 that is in both ranges.

解决此问题的常用方法是使用半开间隔，即 [start，end).换句话说， start< = now&&结束>现在.但这在使用完整的日期和时间而不是仅使用日期时更为常见.您可能不需要这样做，但至少应针对您的特定情况考虑一下.

A common way around this problem is to use half-open intervals, [start,end). In other words, start <= now && end > now. But this is more common when using a full date and time instead of just a date. You might not need to do this, but you should at least think about it for your particular scenario.

这篇关于Asp.Net，SQL和TimeZones的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！