JDBC:插入UTC时间戳

huangapple go评论92阅读模式
英文:

JDBC: Insert timestamp in UTC

问题

我的部分表定义(MySQL)如下所示:

+-----------+----------+------+-----+---------+----------------+
| Field     | Type     | Null | Key | Default | Extra          |
+-----------+----------+------+-----+---------+----------------+
| ...       | ...      | ..   | ... | ...     | ...            |
| timestamp | datetime | YES  |     | NULL    |                |
| ...       | ...      | ..   | ... | ...     | ...            |
+-----------+----------+------+-----+---------+----------------+

我想要使用JDBC更新时间戳字段。假设时间戳处于UTC时区。

在我的Java代码中,我按照以下方式获取时间戳字段:

Timestamp datetime = new Timestamp(new Date().getTime());

关于java.util.Date的文档说明如下:
尽管Date类旨在反映协调通用时间(UTC),但根据Java虚拟机的主机环境,它可能不会完全这样做。

不幸的是,在我的Windows 10环境(java版本为"1.8.0_231")中,Date对象反映了我的本地时区。因此,添加到数据库的时间戳是针对本地时区而不是UTC的。


问)如何在UTC中获取时间戳,以便将正确的值添加到数据库中?

英文:

My partial table definition (MySQL) looks as follows:

+-----------+----------+------+-----+---------+----------------+
| Field     | Type     | Null | Key | Default | Extra          |
+-----------+----------+------+-----+---------+----------------+
| ...       | ...      | ..   | ... | ...     | ...            |
| timestamp | datetime | YES  |     | NULL    |                |
| ...       | ...      | ..   | ... | ...     | ...            |
+-----------+----------+------+-----+---------+----------------+

I want to update the timestamp field using JDBC. The assumption is that the timestamp is in UTC.

In my Java code I am obtaining the timestamp field as follows:

Timestamp datetime = new Timestamp(new Date().getTime());

The documentation for the java.util.Date states that:
Although the Date class is intended to reflect coordinated universal time (UTC), it may not do so exactly, depending on the host environment of the Java Virtual Machine.

Unfortunately on my Windows 10 environment (java version "1.8.0_231"), the Date object reflects my local timezone. As a result the timestamp that gets added to the database is for the local timezone, not UTC.


Q) How can I obtain the timestamp in UTC, so that the correct value is added to the database?

答案1

得分: 7

# tl;dr

你在MySQL中使用了错误的数据类型,并且在Java中使用了错误的类。

- 在MySQL中,写入类型为 [`TIMESTAMP`][1] 而不是 `DATETIME` 的列。
- 在Java中,使用 [`java.time.OffsetDateTime`][2] 而不是 `java.sql.Timestamp`
示例代码片段:

```java
myPreparedStatement
.setObject( 
    … ,                       // 指定要填充的占位符 `?`    OffsetDateTime            // JDBC 4.2 及更高版本需要JDBC驱动程序支持将 `java.time.OffsetDateTime` 对象与数据库交换。
    .now( ZoneOffset.UTC )    // 以UTC时间捕获当前时刻(偏移为零小时-分钟-秒)。
)

MySQL中的错误数据类型

> 假设时间戳是在UTC中

你明白在MySQL中的 DATETIME 不是在UTC时间吗?MySQL中的该类型没有时区或UTC偏移的概念。

如果您要跟踪时刻,时间轴上的特定点,那么您在MySQL中选择了错误的数据类型。您应该使用 TIMESTAMP 来跟踪时刻。

Java中的错误类

您正在使用早期版本Java中捆绑的糟糕的日期时间类。这些类在Java 8及更高版本中被现代的 java.time 类取代,这些类在JSR 310中定义。永远不要使用 java.sql.Timestamp 类。

要获取当前的UTC时刻,使用 Instant

Instant instant = Instant.now();

在MySQL中将列定义为类型 TIMESTAMP

可能能够通过JDBC驱动程序写入 Instant。然而,JDBC 4.2 需要支持 OffsetDateTime,但奇怪的是省略了对 Instant 的必要支持。不管怎样,我们可以很容易地进行转换。

OffsetDateTime odt = instant.atOffset( ZoneOffset.UTC );

通过符合JDBC 4.2或更高版本的JDBC驱动程序,通过预准备语句将其写入数据库。

myPreparedStatement.setObject(  , odt );

检索数据。

OffsetDateTime odt = myResultSet.getObject(  , OffsetDateTime.class );

时区问题解决了

如果您按照此处的代码操作,您将不会遇到时区问题。

  • 该代码不依赖于JVM的当前默认时区。
  • 该代码不依赖于数据库服务器的当前默认时区。

JDBC:插入UTC时间戳


关于 java.time

java.time 框架内置于Java 8及更高版本。这些类取代了旧的问题多多的传统 日期时间类,如 java.util.DateCalendarSimpleDateFormat

要了解更多信息,请参阅Oracle教程。并在Stack Overflow上搜索许多示例和解释。规范是 JSR 310

Joda-Time 项目现在处于维护模式,建议迁移到 java.time 类。

您可以直接与数据库交换 java.time 对象。使用符合 JDBC 4.2 或更高版本的 JDBC驱动程序。无需字符串,也不需要 java.sql.* 类。Hibernate 5 和 JPA 2.2 支持 java.time

从哪里获取java.time类?

英文:

tl;dr

You have the wrong data type in MySQL, and the wrong class in Java.

Example code snippet.

myPreparedStatement
.setObject( 
    … ,                       // Specify which placeholder `?` to fill-in.
    OffsetDateTime            // JDBC 4.2 and later requires a JDBC driver support exchanging `java.time.OffsetDateTime` objects with the database.
    .now( ZoneOffset.UTC )    // Capture the current moment as seen in UTC (an offset of zero hours-minutes-seconds). 
)

Wrong data type in MySQL

>The assumption is that the timestamp is in UTC

Do you understand that DATETIME in MySQL is not in UTC? That type in MySQL has no concept of time zone nor offset-from-UTC.

If you are tracking moments, specific points on the timeline, you have the wrong data type in MySQL. You should be using TIMESTAMP to track moments in MySQL.

Wrong Java classes

You are using terrible date-time classes that were bundled with the earliest versions of Java. Those classes were supplanted by the modern java.time classes in Java 8 and later, as defined in JSR 310. Never use the java.sql.Timestamp class.

To get the current moment in UTC, use Instant.

Instant instant = Instant.now() ;

Define your column in MySQL as type TIMESTAMP.

You may be able to write an Instant via your JDBC driver. However, JDBC 4.2 requires support for OffsetDateTime but oddly omits required support for Instant. No matter, we can easily convert.

OffsetDateTime odt = instant.atOffset( ZoneOffset.UTC ) ;

Write that to the database using your JDBC driver compliant with JDBC 4.2 or later, through a prepared statement.

myPreparedStatement.setObject( … , odt ) ;

Retrieval.

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

Time zone problem eliminated

If you follow the code seen here you will have no time zone problems.

  • None of this code depends on the JVM’s current default time zone.
  • None of this code depends on the database server’s current default time zone.

JDBC:插入UTC时间戳


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.

To learn more, see the Oracle Tutorial. And search Stack Overflow for many examples and explanations. Specification is JSR 310.

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

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.

Where to obtain the java.time classes?

答案2

得分: 5

> Q)如何获取UTC中的时间戳

按照以下方式操作:

ZonedDateTime zdt = ZonedDateTime.now(ZoneId.of("Etc/UTC"));
Timestamp timestamp = Timestamp.valueOf(zdt.toLocalDateTime());

备注:

  1. 由于您想要使用时间戳,建议您将字段类型更改为TIMESTAMP。有关更多详细信息,请查看此链接
  2. 停止使用过时的java.util日期时间API,并切换至现代日期时间API
英文:

> Q) How can I obtain the timestamp in UTC

Do it as follows:

ZonedDateTime zdt = ZonedDateTime.now(ZoneId.of("Etc/UTC"));
Timestamp timestamp = Timestamp.valueOf(zdt.toLocalDateTime());

Notes:

  1. Since you want to work with a timestamp, I recommend you change the field type to TIMESTAMP. Check this for more details.
  2. Stop using the outdated java.util date-time API and switch to modern date-time API.

答案3

得分: 1

可以通过以下列定义让MySQL插入一个时间戳:

CREATE TABLE SOME_TABLE_NAME (
     SOME_COLUMN_NAME TIMESTAMP default CURRENT_TIMESTAMP
);
英文:

You can let MySQL to insert a timestamp using this column definition

CREATE TABLE SOME_TABLE_NAME (
     SOME_COLUMN_NAME TIMESTAMP default CURRENT_TIMESTAMP
);

huangapple
  • 本文由 发表于 2020年6月29日 06:06:44
  • 转载请务必保留本文链接:https://go.coder-hub.com/62628725.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定