使用Java / Postgres处理DateTime和时区 [英] Dealing with DateTime and Timezones with Java / Postgres
问题描述
我正在创建一个应用程序，让用户创建一个带有日期的事件。例如-生日，2018年9月25日。 （仅询问用户日期）。我想将这些事件对象存储在我的Postgres数据库中。
I am creating an application that let's a user create an event with a date. For example - Birthday, 25.09.2018. (The user is asked only for the date). I want to store these event objects in my Postgres DB.
在该日期结束时（这意味着在25.09.2018的23:59:59）我想要我的应用程序在事件 expired = true 上设置标志。
At the end of that date (it means at 23:59:59 on 25.09.2018) I want my application to set a flag on the event expired = true.
我的最初想法是每隔一小时运行一个Scheduler，检查事件记录，如果当前服务器时间大于存储的事件时间，请设置该标志。
因此，我的第一个问题是：
调度程序构想是最好的方法吗？还是性能可能有些差，实际上有更好的方法可以做到？我想每小时运行一次的原因是由于不同的时区。总是有23:59:59。
My initial idea is to run a Scheduler every full hour, check the records for events and if the current server time is greater that the stored event time, set the flag. My first question would therefore be: Is the scheduler idea the best way to do it? Or is it maybe a bit poor with performance and there are actually better ways to do it? The reason why I want to run it every hour is because of the different timezones. There always is 23:59:59 somewhere.
其他问题更着重于存储和检查日期信息本身。如何处理时区？假设来自美国的用户在给定日期添加了Birthday事件，而我的服务器位于欧洲。
如何将其存储在PostgresDB中以及如何在调度程序中执行检查？我需要以某种方式将时区存储在数据库中，然后在sscheduler中检查是否在给定的时区中该日期是给定服务器时间的23:59:59。
The other questions are more focused on storing and checking the date information itself. How do I deal with timezones? Say a user from the USA adds a Birthday event on a given date and my server is located in Europe. How do I store it in PostgresDB and how do I perform the check in the scheduler? I need to store the timezone in the DB somehow and then in the sscheduler check if in that given timezone it is that date at 23:59:59 for the given server time.
请帮助，我对此感到非常困惑。一个例子将是惊人的！谢谢！
Please help, I am really confused with this. An example would be amazing! Thanks!
推荐答案
阿尔贝对我来说看起来正确这是我的一些进一步的想法。
The Answer by Albe looks correct to me. Here are some further thoughts of mine.
我希望我的应用在事件expired = true上设置一个标志。
I want my application to set a flag on the event expired = true.
如阿尔伯提到的那样，没有必要记录这一过期事实。只需按日期查询即可查找日期（过期）之前或日期（未过期）之后的记录。
As Albe mentions, there is no need to record this fact of expiration. Simply query by the date to find records before a date (expired) or after a date (not expired).
如果要考虑性能，请进行测试以进行验证。如果有问题，请考虑为日期列建立索引。
If performance is a concern, test to verify. If it is an issue, consider indexing the date column.
如何处理时区？
How do I deal with timezones?
这就是诀窍。我会在这里简单介绍一下，因为已经在这里和其他姐妹网站 https://dba.stackexchange.com >。
That is the trick. I'll be brief here, as this has been handled many times already here and on the sister site, https://dba.stackexchange.com.
首先，请弄清楚您的业务需求是否需要日期或时间间隔。当您直觉时，日期是不精确的。在任何给定时刻，日期都会随时区在全球范围内变化。在巴黎午夜过后的几分钟，法国是新的一天，而在魁北克蒙特利尔仍然是昨天。
First, get clear if your business requirements need a granularity of date or a moment. A date is imprecise as you intuit. For any given moment, the date varies around the globe by time zone. A few minutes after midnight in Paris France is a new day while still "yesterday" in Montréal Québec.
如果您关心的是时刻，请使用类似于SQL标准的带时区的时间戳记。理解SQL标准几乎没有涉及日期时间处理的主题。因此，不同数据库系统中的行为差异很大。在Postgres中，该列中的值始终为UTC。输入随附的任何时区或UTC偏移量信息均用于调整为UTC，然后丢弃。因此，如果您关心原始的区域/偏移量，则必须手动将其存储在其他列中。
If you care about moments, use a column of a type akin to the SQL-standard TIMESTAMP WITH TIME ZONE. Understand that the SQL standard barely touches on the subject of date-time handling. So behavior in different database systems varies widely. In Postgres, a value in that column is always in UTC. Any time zone or offset-from-UTC info provided with an input is used to adjust into UTC, then discarded. So if you care about the original zone/offset, you must manually store that in an additional column.
检索时，该列中的值始终为UTC。但是，您使用的访问工具可能会就如何呈现该价值提出自己的意见。一些工具会动态应用时区。我发现这种行为是不幸的设计选择。尽管有很好的意图，但这种行为会造成该区域是存储数据一部分的错觉。
When retrieved, the value from that column is always in UTC. However the access tool you are using may inject its opinion about how to present that value. Some tools will dynamically apply a time zone. I find this behavior to be an unfortunate design choice. Though well-intentioned that behavior creates the illusion of that zone being part of the stored data.
在Java中，仅使用现代的 java.time 类，而不使用与最早版本捆绑在一起的非常麻烦且有缺陷的旧日期时间类Java。
In Java, use only the modern java.time classes, not the terribly troublesome and flawed old date-time classes bundled with the earliest versions of Java.
从JDBC 4.2开始，您可以使用 PreparedStatement :: setObject <直接与数据库交换 java.time 对象。 / code>和 ResultSet :: getObject 。
As of JDBC 4.2, you can directly exchange java.time objects with your database using PreparedStatement::setObject and ResultSet::getObject.
我的服务器位于欧洲
my server is located in Europe
服务器的位置以及服务器OS和JVM的当前默认时区应与编程和数据库工作无关。永远不要依赖那些默认设置，因为它们不在您的控制范围之内，并且可以在 运行时随时更改。
The location of your server, and the current default time zone of the server’s OS and JVM, should be irrelevant to your programming and database work. Never depend on those default settings as they are out of your control and can be changed at any moment during runtime.
相反，请始终指定您的默认设置明确/期望/预期的时区。通常，您的大部分工作应使用UTC。仅在业务规则要求或向用户展示时才调整为时区。
Instead, always specify your desired/expected time zone explicitly. Generally, most of your work should be in UTC. Adjust into a time zone only when required by business rules or for presentation to the user.
LocalDate ld = LocalDate.of( 2018 , Month.JANUARY , 23 ) ;
ZoneId z = ZoneId.of( "Africa/Tunis" ) ;
ZonedDateTime zdt = ld.atStartOfDay( z ) ;
通过提取 Instant 调整回UTC 。 Instant 是您应该与数据库交换的内容。
Adjust back into UTC by extracting an Instant. An Instant is what you should be exchanging with your database.
Instant instant = zdt.toInstant() ;
调整回区域。
ZonedDateTime zdtAuckland = instant.atZone( ZoneId.of( "Pacific/Auckland" ) ; // Same moment, same point on the timeline, different wall-clock time.
有关时刻的查询，请搜索Stack Overflow以了解半开放方法，其中定义了时间跨度，开始是包含，而结尾是专有。这意味着您使用SQL运算符<$ c $ not c> BETWEEN 。例如，请参见此。
For querying on moments, search Stack Overflow to learn about the Half-Open approach where a span-of-time is defined where the beginning is inclusive while the ending is exclusive. This means you do not use the SQL operator BETWEEN. For example, see this.
提示：使用符合 site：stackoverflow.com 准则的搜索引擎搜索这些类名和概念，不幸的是Stack Overflow内置的搜索功能忽略答案而偏向于问题，例如：> https://duckduckgo.com/?q=site%3Astackoverflow.com+%2B%22Half-Open%22+%2Bjava&t=osx&ia = web
Tip: Search on these class names and concepts using a search engine with a site:stackoverflow.com criterion. The search feature built into Stack Overflow is unfortunately skewed towards the Questions while ignoring the Answers. For example: https://duckduckgo.com/?q=site%3Astackoverflow.com+%2B%22Half-Open%22+%2Bjava&t=osx&ia=web
java.time 框架内置于Java 8及更高版本中。这些类取代了麻烦的旧版日期时间类，例如 java.util.Date ， Calendar ，& SimpleDateFormat 。
The java.time framework is built into Java 8 and later. These classes supplant the troublesome old legacy date-time classes such as java.util.Date, Calendar, & SimpleDateFormat.
Joda-Time 项目，现在处于维护模式 ，建议迁移到 java.time 类。
The Joda-Time project, now in maintenance mode, advises migration to the java.time classes.
要了解更多信息，请参见 Oracle教程 。并在Stack Overflow中搜索许多示例和说明。规范为 JSR 310 。
To learn more, see the Oracle Tutorial. And search Stack Overflow for many examples and explanations. Specification is JSR 310.
您可以直接与数据库交换 java.time 对象。使用符合 java.sql。* 类。
You may exchange java.time objects directly with your database. Use a JDBC driver compliant with JDBC 4.2 or later. No need for strings, no need for java.sql.* classes.
在哪里获取java.time类？
Where to obtain the java.time classes?
- Java SE 8 ， Java SE 9 ， Java SE 10 ，以及后来的
- 内置。
- 具有捆绑实施的标准Java API的一部分。
- Java 9添加了一些次要功能和修复。
- Java SE 8, Java SE 9, Java SE 10, and later
- Built-in.
- Part of the standard Java API with a bundled implementation.
- Java 9 adds some minor features and fixes.
- 许多java.time功能都被反向移植到Java 6& 7在> ThreeTen-Backport 中。
- Much of the java.time functionality is back-ported to Java 6 & 7 in ThreeTen-Backport.
- java.time 类的更高版本的Android捆绑实现。
- 对于较早的Android（< 26）， ThreeTenABP 项目改编为> ThreeTen-退回 （如上所述）。请参阅 如何使用ThreeTenABP… 。
- Later versions of Android bundle implementations of the java.time classes.
- For earlier Android (<26), the ThreeTenABP project adapts ThreeTen-Backport (mentioned above). See How to use ThreeTenABP….
ThreeTen-Extra 项目使用其他类扩展了java.time。该项目为将来可能在java.time中添加内容提供了一个试验场。您可能会在这里找到一些有用的类，例如
时间间隔，YearWeek，YearQuarter和更多。这篇关于使用Java / Postgres处理DateTime和时区的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
