Java和MySQL中的时间戳和时区转换 [英] Timestamps and time zone conversions in Java and MySQL

问题描述

我正在与位于不同时区的服务器上开发一个带有MySQL数据库的Java应用程序，我正在尝试在我的数据库中使用DATETIME或TIMESTAMP。

I'm developing a Java application with a MySQL database on a server located in a different time zone from mine, and I am trying to decide between using DATETIME or TIMESTAMP on my database.

在阅读问题后，我应该使用字段'datetime'还是'timestamp'？ ，以及 MySQL文档，我认为TIMESTAMP更适合我，因为它将值转换为UTC进行存储，然后返回到当前时区进行检索。

After reading questions like Should I use field 'datetime' or 'timestamp'?, and the MySQL documentation, I decided TIMESTAMP was better for me as it converts values to UTC for storage, and back to the current time zone for retrieval.

此外，正如用户Jesper在这个线程，java.util.Date对象在内部只是一个UTC时间戳（即自Epoch以来的毫秒数，当你执行toString（）时，它会根据你当前的时区显示。

Also, as user Jesper explains in this thread, java.util.Date objects are internally only a UTC timestamp (i.e. number of milliseconds since the Epoch), and when you do a toString() it is displayed according to your current time zone.

对我来说，那个看起来是一个很好的做法：将日期时间存储为UTC时间戳，然后根据当前时区显示它们。

For me, that looks like a good practice: storing datetimes as UTC timestamps, and then displaying them according to the current time zone.

我就是这样做的，但后来我从 Java文档并且非常困惑：

I was about to do it like that, but then I found this from the Java documentation for Prepared Statements and got very confused:

void setTimestamp （int parameterIndex，
Timestamp x，
Calendar cal）
throws SQLException

void setTimestamp(int parameterIndex, Timestamp x, Calendar cal) throws SQLException

将指定参数设置为给定的java.sql。使用给定Calendar对象的时间戳值
。驱动程序使用Calendar对象
构造一个SQL TIMESTAMP值，然后驱动程序将数据库发送到
。使用Calendar对象，驱动程序可以计算
时间戳，同时考虑自定义时区。如果未指定Calendar对象
，则驱动程序将使用默认时区，即运行应用程序的虚拟机
。

Sets the designated parameter to the given java.sql.Timestamp value, using the given Calendar object. The driver uses the Calendar object to construct an SQL TIMESTAMP value, which the driver then sends to the database. With a Calendar object, the driver can calculate the timestamp taking into account a custom timezone. If no Calendar object is specified, the driver uses the default timezone, which is that of the virtual machine running the application.

在此之前，我认为时间戳总是以UTC为惯例。为什么人们想要一个本地化的时间戳而不是本地化的表示呢？这对每个人来说都不会让人感到困惑吗？

Before this, I thought timestamps were by convention always in UTC. Why on earth would anyone want a localized timestamp instead of a localized representation of it? Wouldn't that be very confusing for everyone?

这些转换如何运作？如果Java采用UTC时间戳并将其转换为任意时区，它如何告诉MySQL它在哪个时区？

How do these conversions work? If Java takes an UTC timestamp and converts it to an arbitrary time zone, how can it tell MySQL in which timezone it is?

MySQL不会假设此时间戳是在UTC中，然后检索不正确的本地化值？

Won't MySQL assume that this timestamp is in UTC and then retrieve an incorrect localized value?

推荐答案

日期时间处理是一个混乱

Teo的回答中的第一段非常有见地和正确：日期时间处理Java是一团糟。同上所有其他语言&我所知道的开发环境。日期时间工作既困难又棘手，尤其容易出错且令人沮丧，因为我们直观地认为日期时间。但是，直观地并没有削减数据类型，数据库，序列化，本地化，跨时区调整以及计算机编程带来的所有其他手续。

Date-Time Handling Is A Mess

The first paragraph in the answer by Teo is quite insightful and correct: Date-time handling in Java is a mess. Ditto for all other languages & development environments that I know of. Date-time work is difficult and tricky, especially error-prone and frustrating because we think it of date-time intuitively. But "intuitively" does not cut it when it comes to data types, databases, serialization, localization, adjusting across time zones, and all the other formalities that come with computer programming.

不幸的是，计算机行业基本上选择忽略日期工作的这个问题。正如鉴于明显的需要，Unicode花了很长时间才被发明，因此行业也在解决日期时间处理问题方面的努力。

Unfortunately, the computer industry basically chose to ignore this problem of date-time work. Just as Unicode took too long to be invented given the obvious need, so too has the industry kicked the can down the road on solving the problem of date-time handling.

但我必须不同意其结论。使用count-since-epoch并不是最好的解决方案。使用count-since-epoch本质上容易混淆，容易出错且不兼容。

But I must disagree with its conclusion. Working with a count-since-epoch is not the best solution. Using count-since-epoch is inherently confusing and error-prone and incompatible.

• 人类无法读取 long 数字并将其解读为日期时间。因此，至少可以说，验证数据和调试变得复杂。


• 你会使用什么数量？ 毫秒 /8/docs/api/java/util/Date.html\"rel =nofollow noreferrer> java.util.Date 和 Joda-Time ？ 微秒 =nofollow noreferrer> Postgres ，MySQL和其他数据库？新的纳秒 Java 8中的/javase/8/docs/api/java/time/package-summary.htmlrel =nofollow noreferrer> java.time包？


• 其中您会使用纪元吗？ 1970年初UTC的 Unix纪元很常见，但远非单数。各种计算机系统已经使用了几乎二十几个时代。

• Humans cannot read a long number and decipher that as a date-time. So verifying data and debugging becomes complicated, to say the least.
• What "count" would you use? The milliseconds used by java.util.Date and by Joda-Time? The microseconds used by Postgres, MySQL, and other databases? The nanoseconds used by the new java.time package in Java 8?
• Which epoch would you use? The Unix epoch of the beginning of 1970 in UTC is common, but far from singular. Almost two dozen epochs have been used by various computer systems.

我们创建数字数据类型来进行数学运算而不是使用位。我们创建字符串类来处理处理文本而不是裸八位字节的细节。我们也应该创建数据类型和类来处理日期时间值。

We create numeric data types for doing math rather than using bits. We create string classes to handle the nitty-gritty details of handling text rather than bare octets. So too we should create data-types and classes to handle date-time values.

早期Java团队（和IBM& Taligent 在他们之前）尝试使用java.util.Date和java.util.Calendar及相关类。不幸的是，这种尝试是不够的。虽然日期时间本质上令人困惑，但这些类增加了更多的混乱。

The early Java teams (and IBM & Taligent before them) made an attempt with the java.util.Date and java.util.Calendar and related classes. Unfortunately, the attempt was inadequate. While date-time is inherently confusing, these classes have added even more confusion.

As据我所知， Joda-Time 项目是迄今为止的第一个项目 - 时间以彻底，称职和成功的方式。即便如此，Joda-Time的创作者并不完全满意。他们继续创建 java.time包在Java 8中，并使用三个额外项目进行扩展。 Joda-Time和java.time共享相似的概念，但各有不同，每个都有一些优点。

As far as I know, the Joda-Time project was the first project to take on date-time in a thorough, competent, and successful manner. Even so, the creators of Joda-Time were not entirely satisfied. They went on to create the java.time package in Java 8, and extend that work with the threeten-extra project. Joda-Time and java.time share similar concepts but are distinct, each having some advantages.

具体来说，java.util.Date& .Calendar类缺少仅限日期的值，没有时间和时区。并且他们缺少没有日期和时区的仅限时间的值。在Java 8之前，Java团队添加了名为 java.sql.Date 和 java.sql.Time 类，它们是伪装成仅限日期的日期时间值。 Joda-Time和java.time都通过提供 LocalDate 和 LocalTime 类来纠正它。

Specifically, the java.util.Date & .Calendar classes lack date-only values without time-of-day and time zone. And they lack time-only values without date and time zone. Before Java 8, the Java team added the hacks known as the java.sql.Date and java.sql.Time classes which is a date-time value masquerading as a date-only. Both Joda-Time and java.time rectify that by offering LocalDate and LocalTime classes.

另一个具体问题是java.util.Date的分辨率为毫秒，但数据库经常使用微秒或纳秒。在弥合这种差异的不明智的尝试中，早期的Java团队创建了另一个hack，即 java.sql.Timestamp 类。虽然从技术上讲是java.util.Date子类，但它也跟踪小数秒到纳秒的分辨率。因此，当转换为此类型时，您可能会丢失或获得更精细的小数秒粒度，而不会意识到这一事实。所以这可能意味着你期望相等的值不是。

Another specific problem is that java.util.Date has a resolution of milliseconds, but databases frequently use microseconds or nanoseconds. In an ill-advised attempt to bridge this disparity, the early Java team created another hack, the java.sql.Timestamp class. While technically a java.util.Date subclass, it also tracks the fractional seconds to nanosecond resolution. So when converting in and out of this type you may losing or gaining the finer fractional seconds granularity without being conscious of that fact. So that might mean that values you expect to be equal are not.

另一个混乱的来源是SQL数据类型， TIMESTAMP WITH TIME ZONE 。由于时区信息未存储，因此该名称用词不当。将名称视为 TIMESTAMP WITH RESPECT FOR TIME ZONE ，因为任何传递的时区偏移信息用于将日期时间值转换为 UTC 。

Another source of confusion is the SQL data type, TIMESTAMP WITH TIME ZONE. That name is a misnomer as the time zone info is not stored. Think of the name as TIMESTAMP WITH RESPECT FOR TIME ZONE as any passed time zone offset info is used in converting the date-time value to UTC.

具有纳秒分辨率的java.time包具有一定的特定性更好地与数据库通信日期时间数据的功能。

The java.time package with its nanosecond resolution has some specific features to better communicate date-time data with a database.

我可以编写更多内容，但是可以通过搜索StackOverflow来获取这些信息，例如joda，java。 time，sql timestamp和JDBC。

I could write much more, but such information can be gleaned from searching StackOverflow for words such as joda, java.time, sql timestamp, and JDBC.

使用带有JDBC的Joda-Time和 Postgres 的示例。 Joda-Time为不可变对象。 org / wiki / Thread_safetyrel =nofollow noreferrer>线程安全。因此，我们不是改变实例（mutate），而是根据原始值创建一个新实例。

Example using Joda-Time with JDBC with Postgres. Joda-Time uses immutable objects for thread-safety. So rather than alter an instance ("mutate"), we create a fresh instance based on the values of the original.

String sql = "SELECT now();";
…
java.sql.Timestamp now = myResultSet.getTimestamp( 1 );
DateTime dateTimeUtc = new DateTime( now , DateTimeZone.UTC );
DateTime dateTimeMontréal = dateTimeUtc.withZone( DateTimeZone.forID( "America/Montreal" ) );

专注于UTC

Focus On UTC

在此之前，我认为时间戳总是按UTC惯例。为什么人们想要一个本地化的时间戳而不是它的本地化表示？这对每个人来说都不会让人感到困惑吗？

Before this, I thought timestamps were by convention always in UTC. Why on earth would anyone want a localized timestamp instead of a localized representation of it? Wouldn't that be very confusing for everyone?

确实如此。 SQL标准定义了一个 TIMESTAMP WITHOUT TIME ZONE ，它忽略并删除任何包含的时区数据。我无法想象它的用处。这位Postgres专家David E. Wheeler，尽可能多地推荐始终使用 TIMESTAMP WITH TIME ZONE 。 Wheeler引用了一个狭隘的技术异常（分区），甚至在保存到数据库之前说自己将所有值转换为UTC。

Indeed. The SQL standard defines a TIMESTAMP WITHOUT TIME ZONE which ignores and strips away any included time zone data. I cannot imagine the usefulness of that. This Postgres expert, David E. Wheeler, says as much in recommending always using TIMESTAMP WITH TIME ZONE. Wheeler cites one narrow technical exception (partitioning) and even then says to convert all the values to UTC yourself before saving to the database.

最佳做法是工作和存储UTC中的数据，同时调整为本地化时区以呈现给用户。有时您可能想要记住其本地化时区中的原始日期时间数据;如果是这样，除了转换为UTC之外，还要保存该值。

The best practice is to work and store data in UTC while adjusting to localized time zones for presentation to the user. There may be times when you want to remember the original date-time data in its localized time zone; if so, save that value in addition to converting to UTC.

更好的日期时间处理的第一步是避免java.util.Date& .Calendar，使用Joda-Time和/或java.time，专注于UTC，并学习特定JDBC驱动程序和特定数据库的行为（数据库在日期时间处理方面差异很大，尽管有SQL标准）。

The first steps to better date-time handling are avoiding java.util.Date & .Calendar, using Joda-Time and/or java.time, focusing on UTC, and learning the behavior of your specific JDBC driver and your specific database (databases vary widely in their date-time handling despite the SQL standard).

警告：我不使用MySQL（我是 Postgres 那种人。）

Caveat: I don’t use MySQL (I'm a Postgres kind of guy).

根据版本8文档，两种类型 DATETIME 和 TIMESTAMP 的区别在于第一个缺少任何时区概念或从UTC偏移。第二个使用伴随输入的任何时区或UTC偏移的指示来将该值调整为UTC，然后存储它，并丢弃区域/偏移信息。

According to the version 8 documentation, the two types DATETIME and TIMESTAMP differ in that the first one lacks any concept of time zone or offset-from-UTC. The second one uses any indication of time zone or offset-from-UTC accompanying an input to adjust that value to UTC, then stores it, and discards the zone/offset info.

所以这两种类型似乎类似于标准的SQL类型：

So these two types seem to be akin to the standard SQL types:

• MySQL DATETIME ≈SQL-standard TIMESTAMP WITHOUT TIME ZONE


• MySQL TIMESTAMP ≈SQL-standard TIMESTAMP WITH TIME ZONE

• MySQL DATETIME ≈ SQL-standard TIMESTAMP WITHOUT TIME ZONE
• MySQL TIMESTAMP ≈ SQL-standard TIMESTAMP WITH TIME ZONE

对于MySQL DATETIME ，请使用Java类 LocalDateTime 。该类与该数据类型一样，故意缺少任何时区概念或从UTC偏移。将此类型和类用于：

For MySQL DATETIME, use the Java class LocalDateTime. That class, like that data type, purposely lacks any concept of time zone or offset-from-UTC. Use this type and class for either:

• 当您指的是任何区域或所有 >区域，例如圣诞节从2018年12月25日的第一时刻开始。这可以转化为不同地方的不同时刻，因为新的一天早些时候在东部比在西部开始。


• 在未来安排约会或事件时，政治家可能会改变偏移时区，世界各地的政治家都表现出了倾向。在此用法中，您必须在运行时应用时区来动态计算（但不存储）在日历上显示的时刻。这样，即使政客们将时钟重新定义为提前或延迟的分钟/小时，8个月内15:00的牙科预约仍然是15:00。

• When you mean any zone or all zones, such as "Christmas starts on first moment of December 25, 2018". That translates to different moments in different places as a new day dawns earlier in the east than in the west.
• When scheduling appointments or events far enough out in the future that politicians may change the offset of the time zone, for which politicians around the world have shown a proclivity. In this usage, you must at runtime apply a time zone to dynamically calculate, but not store, a moment for display on a calendar. That way, a 15:00 dental appointment in 8 months remains at 15:00 even if politicians redefine the clock to be minutes/hours ahead or behind.

对于MySQL TIMESTAMP ，使用Java类 Instant ，如上所示。使用此类型和类的时刻，时间轴上的特定点。

For MySQL TIMESTAMP, use the Java class Instant, as shown above. Use this type and class for moments, specific point on the timeline.

从JDBC 4.2开始之后，我们可以直接与数据库交换 java.time 对象。使用 getObject & setObject methods。

As of JDBC 4.2 and later, we can directly exchange java.time objects with the database. Use getObject & setObject methods.

myPreparedStatement.setObject( … , Instant.now() ) ;

检索。

Instant instant = myResultSet.getObject( … , Instant.class ) ;

然后您可以在 Instant 到特定时区以便呈现给用户。

You can then adjust that UTC value in Instant to a specific time zone for presentation to a user.

ZoneId z = ZoneId.of( "Pacific/Auckland" ) ;
ZonedDateTime zdt = instant.atZone( z ) ;

---

关于 java.time

java.time 框架内置于Java 8及更高版本中。这些类取代了麻烦的旧遗留日期时间类，例如 java.util.Date ， 日历 ，& SimpleDateFormat 。

---

About java.time

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 classes。

The Joda-Time project, now in maintenance mode, advises migration to the java.time classes.

要了解更多信息，请参阅 Oracle Tutorial 。并搜索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 对象。使用符合 JDBC驱动程序 / jeps / 170rel =nofollow noreferrer> JDBC 4.2 或更高版本。不需要字符串，不需要 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- Backport （如上所述）。请参阅 如何使用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的试验场。您可以在这里找到一些有用的课程，例如 Interval ， YearWeek ， YearQuarter ，更多。

    这篇关于Java和MySQL中的时间戳和时区转换的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！