将约会存储在诸如Postgres之类的SQL数据库中以与java.time框架一起使用 [英] Storing appointments in a SQL database such as Postgres for use with java.time framework

问题描述

假设我们在意大利米兰进行了约会，日期为01/23/2 21:00欧洲/罗马".该约会将以类似于SQL标准类型TIMESTAMP WITH TIME ZONE的类型的列保存到UTC中的数据库.

Let's say that we have an appointment in Milan Italy happening on 01/23/2021 21:00 "Europe/Rome". This appointment is saved to the database in UTC in a column of a type akin to the SQL-standard type TIMESTAMP WITH TIME ZONE.

现在，居住在美国纽约的用户需要了解此次约会的时间.我们可以向用户显示转换为"America/New_York"的日期时间.时区，或改为在欧洲/罗马"中显示； TZ.一旦用户从纽约飞往米兰，他会发现这两个信息都很有用.

Now a user living in New York US needs to understand when this appointment will take place. We can show that user the date-time converted to "America/New_York" time zone, or instead, in "Europe/Rome" TZ. Once the user will fly from New York to Milan, he will find both info useful.

关键是要存储所有转换为相同的TZ参考(UTC)的内容，并使用

The point is to store everything converted into the same TZ reference (UTC), and the manipulate the date-time depending on the goal you have using the java.time framework bundled with modern Java.

感觉还是有什么问题/遗漏?

Makes sense or there is something wrong/missing?

推荐答案

感觉还是有些错误/遗漏?

Makes sense or there is something wrong/missing?

这取决于约会的类型.

约会有两种:

• 片刻，时间线上的特定点，忽略了对时区规则的任何更改.
  示例:火箭发射.
• 应该根据时区规则的更改而调整的日期和时间.
  示例:医疗/牙科就诊.

例如，如果我们要预订火箭的发射，则我们不在乎日期和时间.我们只关心(a)天成一直线，并且(b)我们期望有利的天气.

If we are booking the launch of a rocket, for example, we do not care about the date and the time-of-day. We only care about the moment when (a) the heavens align, and (b) we expect favorable weather.

如果在此期间政客更改了我们发射场或办公室使用的时区规则，则对我们的发射约会没有影响.如果管理我们启动站点的政客采用夏令时(DST)，则启动的时间仍然存在相同.如果管理我们办公室的政客决定由于与邻国的外交关系而将时钟提前了半个小时，我们的发射时刻保持不变.

If in the intervening time the politicians change the rules of the time zone in use at our launch site or at our offices, that has no effect on our launch appointment. If politicians governing our launch site adopt Daylight Saving Time (DST), the moment of our launch remains the same. If the politicians governing our offices decide to change the clock a half-hour earlier because of diplomatic relations with a neighboring country, the moment of our launch remains the same.

对于这样的约会，是的，您的方法是正确的.您将使用类型为TIMESTAMP WITH TIME ZONE的列将约会记录记录在 UTC 中.检索后，调整到用户喜欢的任何时区.

For such an appointment, yes, your approach would be correct. You would record the appointment in UTC using a column of type TIMESTAMP WITH TIME ZONE. Upon retrieval, adjust into any time zone the user prefers.

诸如 Postgres 之类的数据库会使用输入随附的任何时区信息来调整为UTC，然后处理该时区信息.当您从Postgres检索值时，它将始终以UTC中的时间表示日期.当心，某些工具或中间件可能具有在从数据库检索到向程序员交付之间应用一些默认时区的反特征.但请注意:Postgres始终以UTC(始终为UTC)和 offset- from-UTC 为零时分-秒.

Databases such as Postgres use any time zone info accompanying an input to adjust into UTC, and then disposes of that time zone info. When you retrieve the value from Postgres, it will always represent a date with time-of-day as seen in UTC. Beware, some tooling or middleware may have the anti-feature of applying some default time zone between retrieval from database and delivery to you the programmer. But be clear: Postgres always saves and retrieves values of type TIMESTAMP WITH TIME ZONE in UTC, always UTC, and offset-from-UTC of zero hours-minutes-seconds.

这是一些Java示例代码.

Here is some example Java code.

LocalDate launchDateAsSeenInRome = LocalDate.of( 2021 , 1 , 23 ) ;
LocalTime launchTimeAsSeenInRome = LocalTime.of( 21 , 0 ) ;
ZoneId zoneEuropeRome = ZoneId.of( "Europe/Rome" ) ;
// Assemble those three parts to determine a moment.
ZonedDateTime launchMomentAsSeenInRome = ZonedDateTime.of( launchDateAsSeenInRome , launchTimeAsSeenInRome , zoneEuropeRome ) ;

launchMomentAsSeenInRome.toString():2021-01-23T21:00 + 01:00 [欧洲/罗马]

launchMomentAsSeenInRome.toString(): 2021-01-23T21:00+01:00[Europe/Rome]

要在UTC中查看同一时刻，请转换为Instant. Instant对象始终代表UTC所看到的时刻.

To see the same moment in UTC, convert to an Instant. An Instant object always represents a moment as seen in UTC.

Instant launchInstant = launchMomentAsSeenInRome.toInstant() ;  // Adjust from Rome time zone to UTC.

launchInstant.toString():2021-01-23T20:00:00Z

launchInstant.toString(): 2021-01-23T20:00:00Z

上面的字符串示例结尾的Z是UTC的标准符号，发音为"Zulu".

The Z on the end of the above string example is standard notation for UTC, and is pronounced "Zulu".

不幸的是，JDBC 4.2团队忽略了要求支持 JDBC驱动程序可能会也可能无法将此类对象读/写到数据库中.如果不是，只需将其转换为 JDBC

Unfortunately the JDBC 4.2 team neglected to require support for either Instant or ZonedDateTime types. So your JDBC driver may or may not be able to read/write such objects to your database. If not, simply convert to OffsetDateTime. All three types represent a moment, a specific point on the timeline. But OffsetDateTime has support required by JDBC 4.2 for reasons that escape me.

OffsetDateTime odtLaunchAsSeenInRome = launchMomentAsSeenInRome.toOffsetDateTime() ;

写入数据库.

myPreparedStatement.setObject( … , odtLaunchAsSeenInRome ) ;

从数据库中检索.

OffsetDateTime launchMoment = myResultSet.getObject( … , OffsetDateTime.class ) ;

调整为用户所需的纽约时区.

Adjust to the New York time zone desired by your user.

ZoneId zoneAmericaNewYork = ZoneId.of( "America/New_York" ) ;
ZonedDateTime launchAsSeenInNewYork = launchMoment.atZoneSameInstant( zoneAmericaNewYork ) ;

launchAsSeenInNewYork.toString():2021-01-23T15:00-05:00 [美国/纽约]

launchAsSeenInNewYork.toString(): 2021-01-23T15:00-05:00[America/New_York]

您可以在IdeOne.com上实时查看上述所有代码运行.

You can see all of the above code run live at IdeOne.com.

顺便说一句，跟踪过去的事件也被视为片刻.患者实际上是什么时候到达预约的，客户何时支付发票，新员工何时签署文件，服务器何时崩溃……所有这些都在UTC中被跟踪.如上所述，尽管ZonedDateTime& OffsetDateTime也代表片刻.对于数据库，请使用TIMESTAMP WITH TIME ZONE(而不是WITHOUT).

By the way, tracking past events are also treated as a moment. When did the patient actually arrive for appointment, when did the customer pay the invoice, when did a new hire sign their documents, when did the server crash… all these are tracked as a moment in UTC. As discussed above, usually that would be an Instant, though ZonedDateTime & OffsetDateTime also represent a moment. For the database use TIMESTAMP WITH TIME ZONE (not WITHOUT).

我希望大多数面向业务的应用程序都将重点放在其他类型的约会上，我们的目标是具有日期的日期而不是特定的时刻.

I expect most business-oriented apps are focused on appointments of the other type, where we aim at a date with a time-of-day rather than a specific moment.

如果用户与他们的医疗保健提供者约会以检查测试结果，那么他们将在该日期的特定时间进行检查.如果与此同时，政客更改了时区规则，将时钟向前或向后移动了一个小时或半小时或任何其他时间，则该医疗约会的日期和时间将保持不变.实际上，当政客更改时区后，原先约会的时间点将更改为时间点上的较早/较晚的点.

If the user makes an appointment with their health care provider to review the results of a test, they do so for a particular time-of-day on that date. If in the meantime the politicians change the rules of their time zone, moving the clock ahead or behind an hour or half-hour or any other amount of time, the date and time-of-day of that medical appointment remain the same. In actuality, the point of the timeline of the original appointment will be changed after the politicians change the time zone, shifting to an earlier/later point on the timeline.

对于此类约会，我们不会存储(em> not )，如UTC所示.我们不使用数据库列类型TIMESTAMP WITH TIME ZONE.

For such appointmentss, we do not store the date and time-of-day as seen in UTC. We do not use the database column type TIMESTAMP WITH TIME ZONE.

对于此类约会，我们将日期与日期存储在一起，而不考虑时区.我们使用类型为TIMESTAMP WITHOUT TIME ZONE的数据库列(注意WITHOUT而不是WITH). Java中的匹配类型为LocalDateTime.

For such appointments, we store the date with time-of-day without any regard to time zone. We use a database column of type TIMESTAMP WITHOUT TIME ZONE (notice WITHOUT rather than WITH). The matching type in Java is LocalDateTime.

LocalDate medicalApptDate = LocalDate.of( 2021 , 1 , 23 ) ;
LocalTime medicalApptTime = LocalTime.of( 21 , 0 ) ;
LocalDateTime medicalApptDateTime = LocalDateTime.of( medicalApptDate , medicalApptTime ) ;

将其写入数据库.

myPreparedStatement.setObject( … , medicalApptDateTime ) ;

请注意以下几点:LocalDateTime对象不是 not 代表时刻，还是 not 在时间轴上的特定点. LocalDateTime对象代表沿时间轴(全球时区的范围)约26-27小时的一系列可能时刻.要赋予LocalDateTime真实的含义，我们必须关联一个预期的时区.

Be clear on this: a LocalDateTime object does not represent a moment, is not a specific point on the timeline. A LocalDateTime object represents a range of possible moments along about 26-27 hours of the timeline (the range of time zones around the globe). To give real meaning to a LocalDateTime, we must associate an intended time zone.

对于该预期时区，请使用第二列来存储时区标识符.例如，字符串Europe/Rome或America/New_York.请参见区域名称列表.

For that intended time zone, use a second column to store the zone identifier. For example, the strings Europe/Rome or America/New_York. See list of zone names.

ZoneId zoneEuropeRome = ZoneId.of( "Europe/Rome" ) ;

以文本形式将其写入数据库.

Write that to the database as text.

myPreparedStatement.setString( … , zoneEuropeRome ) ;

检索.以文本形式检索区域名称，并实例化ZoneId对象.

Retrieval. Retrieve the zone name as text, and instantiate a ZoneId object.

LocalDateTime medicalApptDateTime = myResultSet.getObject( … , LocalDateTime.class ) ;
ZoneId medicalApptZone = ZoneId.of( myResultSet.getString( … ) ) ;

将这两部分放在一起，以确定表示为ZonedDateTime对象的时刻.当您需要安排日历时，可以动态执行此操作.但是不存储该时刻.如果政客们将来重新定义时区，那么必须计算一个不同的时刻.

Put those two pieces together to determine a moment represented as a ZonedDateTime object. Do this dynamically when you need to schedule a calendar. But do not store the moment. If the politicians redefine the time zone(s) in the future, a different moment must be calculated then.

ZonedDateTime medicalApptAsSeenInCareProviderZone = ZonedDateTime.of( medicalApptDateTime , medicalApptZone ) ;

用户正在前往美国纽约.他们需要根据纽约临时所在地墙上的时钟，知道何时致电意大利米兰的医疗保健提供者.因此，从一个时区调整到另一个时区.相同的时刻，不同的时钟时间.

The user is traveling to New York US. They need to know when to call the health care provider in Milan Italy according to the clocks on the wall in their temporary location of New York. So adjust from one time zone to another. Same moment, different wall-clock time.

ZoneId zoneAmericaNewYork = ZoneId.of( "America/New_York" ) ;
ZonedDateTime medicalApptAsSeenInNewYork = medicalApptAsSeenInCareProviderZone.withZoneSameInstant( zoneAmericaNewYork ) ;

---

tzdata

请注意，如果所需时区的规则可能正在更改，则必须在计算机上更新时区定义的副本.

---

tzdata

Be aware that if the rules of your desired time zones may be changing, you must update the copy of time zone definitions on your computers.

Java包含自己的 tzdata 的副本.做Postgres数据库引擎.以及您的主机操作系统.此处显示的特定代码仅要求Java是最新的.如果您使用Postgres进行时区调整，则其 tzdata 也必须是最新的.对于日志记录等，您的主机操作系统应该保持最新.为了使用户能够正确观察时钟，他们的客户端计算机的操作系统也必须是最新的.

Java contains its own copy of the tzdata, as does the Postgres database engine. And your host operating system as well. This particular code shown here requires only Java to be up-to-date. If you use Postgres to make time zone adjustments, its tzdata must also be up-to-date. And for logging and such, your host OS should be kept up-to-date. For proper clock-watching by the user, their client machine's OS must also be up-to-date.

当心:世界各地的政客都对改变时区表现出浓厚的兴趣，他们常常以惊人的频率来改变他们的时区，而且往往很少预警.

Beware: Politicians around the world have shown a penchant for changing their time zones with surprising frequency, and often with little forewarning.

旧版日期时间类，例如 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.

要了解更多信息，请参见 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.

Joda-Time 项目，现在位于<一个href ="https://en.wikipedia.org/wiki/Maintenance_mode" rel ="nofollow noreferrer">维护模式，建议迁移到您可以直接与数据库交换 java.time 对象.使用符合 JDBC驱动程序 /jeps/170"rel =" nofollow noreferrer> JDBC 4.2 或更高版本.不需要字符串，不需要java.sql.*类.休眠5& JPA 2.2支持 java.time .

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. Hibernate 5 & JPA 2.2 support java.time.

在哪里获取java.time类?

Where to obtain the java.time classes?

• Java SE 8 ， Java SE 9 ， Java 9 带来了一些次要功能和修复

• Java SE 8, Java SE 9, Java SE 10, Java SE 11, and later - Part of the standard Java API with a bundled implementation.
  • Java 9 brought some minor features and fixes.
  • 大多数 java.time 功能都向后移植到Java 6& 7在 ThreeTen-Backport . /li>
  • Most of the java.time functionality is back-ported to Java 6 & 7 in ThreeTen-Backport.
  • 较新版本的Android(26+)捆绑了 java.time 类的实现.
  • 对于较早的Android(< 26)，此过程称为 API废止 带来了子集 java.time 功能最初不是Android内置的.
    • 如果讨价还价不能满足您的需求，请 ThreeTenABP 项目适应> ThreeTen-Backport > (如上所述)安装到Android.请参阅 如何使用ThreeTenABP… .
    • Later versions of Android (26+) bundle implementations of the java.time classes.
    • For earlier Android (<26), a process known as API desugaring brings a subset of the java.time functionality not originally built into Android.
      • If the desugaring does not offer what you need, the ThreeTenABP project adapts ThreeTen-Backport (mentioned above) to Android. See How to use ThreeTenABP….

      这篇关于将约会存储在诸如Postgres之类的SQL数据库中以与java.time框架一起使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！