oracle 如何在存储日期和时间的应用程序中处理多个 TimeZones？

How does one deal with multiple TimeZones in applications that store dates and times?

提问by Nate

I realize this is a bit subjective, but I'm hoping to pick everyones brain here on how they deal with multiple timezones? There are a variety of similar questions here and an equally wide variety of accepted answers.

我意识到这有点主观，但我希望在这里挑选每个人的大脑，了解他们如何处理多个时区？这里有各种类似的问题，也有同样广泛的公认答案。

How have youdelt with this in apps you've built, and what issues did you have to overcome?

怎么会有你这种衣食住行中的应用程序，你已经建立，并且你必须克服哪些问题？

回答by kubal5003

You always store date/time in one time zone(9 out of 10 it is London time) and convert it on display to the timezone of your user. This is strictly application level issue not the db.

您总是将日期/时间存储在一个时区（十分之九是伦敦时间）并将其显示为您用户的时区。这是严格的应用程序级别问题，而不是数据库。

回答by Escualo

At work we manage several clocks at once, not only time zones but also some more esoteric clocks used for spacecraft navigation.

在工作中，我们同时管理多个时钟，不仅是时区，还有一些用于航天器导航的更深奥的时钟。

The only things that reallymatter are: that you are consistent with ONE AND ONLY ONE CLOCK, whichever you pick, and that you have the APPROPRIATE CLOCK CONVERSIONSfor when you need a VIEW OF TIMEin a CLOCK DIFFERENT FROM YOUR ONE AND ONLY ONE CLOCK.

唯一真正重要的是：您与ONE AND ONLY ONE CLOCK保持一致，无论您选择哪个，并且当您需要在与您的 ONE AND ONLY ONE CLOCK 不同的时钟中查看时间时，您有适当的时钟转换.

So:

所以：

One and Only One Clock: Pick the simplest one which will solve your problem, most likely this will be UTC(what some people would -incorrectly- call Greenwich, but the point remains: the zero line).

一个且只有一个时钟：选择最简单的一个可以解决您的问题，很可能这将是UTC（有些人会 - 错误地将其称为格林威治，但重点仍然是：零线）。

Appropriate clock conversions: This depends on your application, but you need to ask and answer the following 2 questions: How much resolution do I need? Do I need to make sure I account for leap seconds? Once you answered, you may be able to pick standard libraries or more estoreric ones. Again, you must ask these questions.

适当的时钟转换：这取决于您的应用，但您需要提出并回答以下 2 个问题：我需要多少分辨率？我需要确保我考虑了闰秒吗？回答后，您可以选择标准库或更多存储库。同样，你必须问这些问题。

View of time: when someone picks a view of time (say, Pacific Time) simply call the appropriate clock conversionon demand.

时间视图：当有人选择时间视图（例如太平洋时间）时，只需根据需要调用适当的时钟转换。

Really, that's it.

真的，就是这样。

As for libraries, I use Python for scripting, but NAIF Spice Library for mission design, and in-house code for spacecraft navigation. The difference between them is simply the resolution and the reliability on having accounted for everything you need to account for (Earth rotation, Relativity, time dilation, leap seconds, etc.) Of course, you will pick the library that fits your needs.

至于库，我使用 Python 编写脚本，但使用 NAIF Spice 库进行任务设计，以及用于航天器导航的内部代码。它们之间的区别只是分辨率和可靠性，包括您需要考虑的一切（地球自转、相对论、时间膨胀、闰秒等）。当然，您将选择适合您需求的库。

Good luck.

祝你好运。

Edit:

编辑：

I forgot to mention: don't try to implement your own time management library - use an off the shelve one. If you try, you may be successful, but your realproject will die and you will only have one averagedate time library to show for it. Maybe I'm being exaggerated, but making a solid, general purpose date time library is far from trivial, i.e., it is a project in itself.

我忘了提：不要尝试实现自己的时间管理库 - 使用现成的。如果您尝试，您可能会成功，但是您的真实项目将会消亡，您将只有一个平均日期时间库来显示它。也许我被夸大了，但是制作一个可靠的、通用的日期时间库绝非易事，也就是说，它本身就是一个项目。

回答by Chris Diver

One of the projects i'm working on i'm using SQL Server 2005 and GETUTCDATE()to store the date in UTC format.

我正在从事的项目之一是使用 SQL Server 2005 并GETUTCDATE()以 UTC 格式存储日期。

回答by gsoni

You need to maintain the user timezone information and all time zone information. And always store the time in UTC.And when you want to display that information to particular user then just access this user's timezone(which is stored for this user) and add or subtract that amount of time from the time stored in UTC and display this.That will be in the timezone of the user who is viewing the information.

您需要维护用户时区信息和所有时区信息。并始终以 UTC 格式存储时间。当您想向特定用户显示该信息时，只需访问该用户的时区（为该用户存储），然后从以 UTC 格式存储的时间中添加或减去该时间量并显示该时间.那将是查看信息的用户所在的时区。

回答by Jason Horner

I think the new best practice with sql server 2008 is to always use the datetimeoffset datatype. normalizing dates to UTC is also good practices; but not always feasible or desirable.

我认为 sql server 2008 的新最佳实践是始终使用 datetimeoffset 数据类型。将日期标准化为 UTC 也是很好的做法；但并不总是可行或可取的。

See this blog post for more thoughts: http://blogs.msdn.com/b/bartd/archive/2009/03/31/the-death-of-datetime.aspx

有关更多想法，请参阅此博客文章：http: //blogs.msdn.com/b/bartd/archive/2009/03/31/the-death-of-datetime.aspx

回答by devstuff

+1 for @kubal5003.

+1 @kubal5003。

Display of dates and times is always complicated by culture and time zone, so its always best to use the layer closest to the user (e.g. the browser or local application) to do this. It also moves some of the load from the database to the user's machine.

日期和时间的显示总是受文化和时区的影响，因此最好使用离用户最近的层（例如浏览器或本地应用程序）来执行此操作。它还将一些负载从数据库转移到用户的机器上。

There is an exception for server-generated reports though. So I store the time zone name/ID (occasionally just the offset/bias) to find the start of day. This may be system-wide or on a per client/brand basis.

不过，服务器生成的报告有一个例外。所以我存储时区名称/ID（有时只是偏移/偏差）以找到一天的开始。这可能是系统范围的或基于每个客户/品牌的。

For web applications I usually detect a user's default time zone via geolocation (this is rarely wrong since geo data is quite accurate now).

对于 Web 应用程序，我通常通过地理定位检测用户的默认时区（这很少出错，因为现在地理数据非常准确）。