在 Java 中处理 MySQL 日期时间和时间戳 [英] Handling MySQL datetimes and timestamps in Java

问题描述

在 java 应用程序中，在使用日期时间和时间戳的混合使用 MySQL 数据库提取和输入日期信息方面，有什么好的折衷方案?

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 方面，日期通常由(设计不佳，但除此之外)java.util.Date.它基本上由 Epoch time 支持，具有 long 的味道，也称为时间戳.它包含有关日期和时间部分的信息.在 Java 中，精度以毫秒为单位.

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 time in 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.

在 SQL 端，有几种标准的日期和时间类型，DATE、TIME 和 TIMESTAMP(在某些数据库中也称为 DATETIME)，在 JDBC 中表示为 java.sql.Date, java.sql.Time 和 java.sql.Timestamp，java.util.Date 的所有子类.精度取决于数据库，通常像 Java 一样以毫秒为单位，但也可以以秒为单位.

In SQL side, there are several standard date and time types, DATE, TIME and TIMESTAMP (at some DB's also called DATETIME), which are represented in JDBC as java.sql.Date, java.sql.Time and java.sql.Timestamp, all subclasses of java.util.Date. The precision is DB dependent, often in milliseconds like Java, but it can also be in seconds.

与 java.util.Date 不同，java.sql.Date 仅包含有关日期部分(年、月、日)的信息.Time 仅包含有关时间部分(小时、分钟、秒)的信息，Timestamp 包含有关这两个部分的信息，例如 java.util.Date 确实如此.

In contrary to java.util.Date, the java.sql.Date contains only information about the date part (year, month, day). The Time contains only information about the time part (hours, minutes, seconds) and the Timestamp contains information about the both parts, like as java.util.Date does.

在 DB 中存储时间戳的正常做法(因此，java.util.Date 在 Java 端和 java.sql.Timestamp 在 JDBC 端)是使用 PreparedStatement#setTimestamp().

The normal practice to store a timestamp in the DB (thus, java.util.Date in Java side and java.sql.Timestamp in JDBC side) is to use 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);

从数据库获取时间戳的通常做法是使用 ResultSet#getTimestamp().

The normal practice to obtain a timestamp from the DB is to use ResultSet#getTimestamp().

Timestamp timestamp = resultSet.getTimestamp("ts");
java.util.Date date = timestamp; // You can just upcast.

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