英文:
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的当前默认时区。
- 该代码不依赖于数据库服务器的当前默认时区。
关于 java.time
java.time 框架内置于Java 8及更高版本。这些类取代了旧的问题多多的传统 日期时间类,如 java.util.Date
、Calendar
和 SimpleDateFormat
。
要了解更多信息,请参阅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类?
-
Java SE 8、Java SE 9、Java SE 10、Java SE 11 以及更高版本 - 作为标准Java API的一部分,附带有捆绑的实现。
-
Java 9 添加了一些次要功能和修复。
-
大部分 java.time 功能在Java 6和 7 中进行了ThreeTen-Backport的后移。
-
- 较新版本的Android捆绑了 java.time 类的实现。
- 对于较早的Android(<26),ThreeTenABP 项目将 ThreeTen-Backport 进行了适配。请参阅 如何使用 ThreeTenABP…。
英文:
tl;dr
You have the wrong data type in MySQL, and the wrong class in Java.
- Write to a column of type
TIMESTAMP
rather thanDATETIME
. - Use
java.time.OffsetDateTime
rather thanjava.sql.Timestamp
.
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.
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?
- 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 adds some minor features and fixes.
- Java SE 6 and Java SE 7
- Most of the java.time functionality is back-ported to Java 6 & 7 in ThreeTen-Backport.
- Android
- 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….
答案2
得分: 5
> Q)如何获取UTC中的时间戳
按照以下方式操作:
ZonedDateTime zdt = ZonedDateTime.now(ZoneId.of("Etc/UTC"));
Timestamp timestamp = Timestamp.valueOf(zdt.toLocalDateTime());
备注:
英文:
> 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:
- Since you want to work with a timestamp, I recommend you change the field type to
TIMESTAMP
. Check this for more details. - 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
);
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论