在 Java 中处理 MySQL 日期时间和时间戳
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3323618/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Handling MySQL datetimes and timestamps in Java
提问by James P.
In a java application what would a good compromise in terms of extracing and inputting date information with a MySQL database using a mix of datetimes and timestamps?
在 java 应用程序中，在使用日期时间和时间戳的混合使用 MySQL 数据库提取和输入日期信息方面，有什么好的折衷方案？
采纳答案by BalusC
In Java side, the date is usually represented by the (poorly designed, but that aside) java.util.Date. It is basically backed by the Epoch timein flavor of a long, also known as a timestamp. It contains information about both the date and time parts. In Java, the precision is in milliseconds.
在 Java 方面，日期通常由 (设计不佳，但除此之外) 表示java.util.Date。它基本上由Epoch 时间以 a 的形式支持long，也称为时间戳。它包含有关日期和时间部分的信息。在 Java 中，精度以毫秒为单位。
In SQL side, there are several standard date and time types, DATE, TIMEand TIMESTAMP(at some DB's also called DATETIME), which are represented in JDBC as java.sql.Date, java.sql.Timeand java.sql.Timestamp, all subclassesof java.util.Date. The precision is DB dependent, often in milliseconds like Java, but it can also be in seconds.
在SQL方面，有几个标准的日期和时间类型，DATE，TIME和TIMESTAMP（在一些DB也叫DATETIME），这是代表在JDBC为java.sql.Date，java.sql.Time和java.sql.Timestamp所有子类的java.util.Date。精度取决于数据库，通常像 Java 一样以毫秒为单位，但也可以以秒为单位。
In contrary to java.util.Date, the java.sql.Datecontains only information about the date part (year, month, day). The Timecontains only information about the time part (hours, minutes, seconds) and the Timestampcontains information about the both parts, like as java.util.Datedoes.
与 相反java.util.Date，java.sql.Date仅包含有关日期部分（年、月、日）的信息。在Time包含有关时间的一部分唯一信息（小时，分钟，秒），Timestamp包含关于两个部分信息，等作为java.util.Date一样。
The normal practice to store a timestamp in the DB (thus, java.util.Datein Java side and java.sql.Timestampin JDBC side) is to use PreparedStatement#setTimestamp().
在 DB（因此，java.util.Date在 Java 端和java.sql.TimestampJDBC 端）中存储时间戳的通常做法是使用PreparedStatement#setTimestamp().
java.util.Date date = getItSomehow();
Timestamp timestamp = new Timestamp(date.getTime());
preparedStatement = connection.prepareStatement("SELECT * FROM tbl WHERE ts > ?");
preparedStatement.setTimestamp(1, timestamp);
The normal practice to obtain a timestamp from the DB is to use ResultSet#getTimestamp().
从数据库获取时间戳的通常做法是使用ResultSet#getTimestamp().
Timestamp timestamp = resultSet.getTimestamp("ts");
java.util.Date date = timestamp; // You can just upcast.
回答by Garett
The MySQL documentationhas information on mapping MySQL types to Java types. In general, for MySQL datetime and timestamps you should use java.sql.Timestamp. A few resources include:
MySQL文档包含有关将 MySQL 类型映射到 Java 类型的信息。通常，对于 MySQL 日期时间和时间戳，您应该使用java.sql.Timestamp. 一些资源包括：
http://dev.mysql.com/doc/refman/5.1/en/datetime.html
http://dev.mysql.com/doc/refman/5.1/en/datetime.html
http://www.coderanch.com/t/304851/JDBC/java/Java-date-MySQL-date-conversion
http://www.coderanch.com/t/304851/JDBC/java/Java-date-MySQL-date-conversion
How to store Java Date to Mysql datetime...?
EDIT:
编辑：
As others have indicated, the suggestion of using strings may lead to issues.
正如其他人所指出的，使用字符串的建议可能会导致问题。
回答by Dorjee
BalusC gave a good description about the problem but it lacks a good end to end code that users can pick and test it for themselves.
BalusC 对问题进行了很好的描述，但它缺乏用户可以自行挑选和测试的良好端到端代码。
Best practice is to always store date-time in UTC timezone in DB. Sql timestamp type does not have timezone info.
最佳做法是始终在 DB 中以 UTC 时区存储日期时间。Sql 时间戳类型没有时区信息。
When writing datetime value to sql db
将日期时间值写入 sql db 时
//Convert the time into UTC and build Timestamp object.
Timestamp ts = Timestamp.valueOf(LocalDateTime.now(ZoneId.of("UTC")));
//use setTimestamp on preparedstatement
preparedStatement.setTimestamp(1, ts);
When reading the value back from DB into java,
当将值从 DB 读回 java 时，
- Read it as it is in java.sql.Timestamp type.
- Decorate the DateTime value as time in UTC timezone using atZone method in LocalDateTime class.
Then, change it to your desired timezone. Here I am changing it to Toronto timezone.
ResultSet resultSet = preparedStatement.executeQuery(); resultSet.next(); Timestamp timestamp = resultSet.getTimestamp(1); ZonedDateTime timeInUTC = timestamp.toLocalDateTime().atZone(ZoneId.of("UTC")); LocalDateTime timeInToronto = LocalDateTime.ofInstant(timeInUTC.toInstant(), ZoneId.of("America/Toronto"));
- 以 java.sql.Timestamp 类型读取它。
- 使用 LocalDateTime 类中的 atZone 方法将 DateTime 值装饰为 UTC 时区中的时间。
然后，将其更改为您想要的时区。在这里，我将其更改为多伦多时区。
ResultSet resultSet = preparedStatement.executeQuery(); resultSet.next(); Timestamp timestamp = resultSet.getTimestamp(1); ZonedDateTime timeInUTC = timestamp.toLocalDateTime().atZone(ZoneId.of("UTC")); LocalDateTime timeInToronto = LocalDateTime.ofInstant(timeInUTC.toInstant(), ZoneId.of("America/Toronto"));
