最适合存储日期和时间的SQL和Java数据类型[复制]

This question already has an answer here:

这个问题已经有了答案:

• Handling MySQL datetimes and timestamps in Java 3 answers
• 在Java 3答案中处理MySQL数据时间和时间戳

Apologies in advance for the somewhat broad question.

先为这个有点宽泛的问题道歉。

What are the most appropriate MySQL and Java data types for handling date and times with the following format: yyyy.MM.dd hh:mm:ss

使用以下格式处理日期和时间最合适的MySQL和Java数据类型是什么?dd hh:mm:ss

I need to be able to convert a String representation of the date & time into the given Date Format as well as then store that date&time in the database. I then need to do the reverse and convert the MySQL date & time into the corresponding Java representation

我需要能够将日期和时间的字符串表示转换为给定的日期格式，然后将该日期和时间存储在数据库中。然后我需要做相反的操作，将MySQL的日期和时间转换为相应的Java表示

I have read a lot of questions about Date, DateTime, ZonedDateTime but none of these seem to work at all well.

我读过很多关于日期、日期时间、ZonedDateTime的问题，但这些似乎都不太管用。

Thanks.

谢谢。

3 个解决方案

#1

What are the most appropriate MySQL and Java data types for handling date and times with the following format: yyyy.MM.dd hh:mm:ss

使用以下格式处理日期和时间最合适的MySQL和Java数据类型是什么?dd hh:mm:ss

The most appropriate MySQL type is DATETIME. See Should I use field 'datetime' or 'timestamp'?.

最合适的MySQL类型是DATETIME。我应该使用字段'datetime'还是'timestamp'?

The corresponding Java type to use in your persistence layer (jdbc type) is java.sql.Timestamp. See Java, JDBC and MySQL Types.

在持久层(jdbc类型)中使用的对应Java类型是Java .sql. timestamp。请参阅Java、JDBC和MySQL类型。

I need to be able to convert a String representation of the date & time into the given Date Format as well as then store that date&time in the database.

我需要能够将日期和时间的字符串表示转换为给定的日期格式，然后将该日期和时间存储在数据库中。

The correct transformation is: java.lang.String -> java.util.Date -> java.sql.Timestamp.

正确的转换是:java.lang。- > java.util字符串。- > java.sql.Timestamp日期。

• java.lang.String -> java.util.Date:
• . lang。字符串- > java.util.Date:

Date javaDatetime = new SimpleDateFormat("yyyy.MM.dd hh:mm:ss").parse(stringDatetime);

日期javaDatetime =新的SimpleDateFormat(“yyyy.MM”)。dd hh:mm:ss).parse(stringDatetime);

• java.util.Date -> java.sql.Timestamp:
• java.util。- > java.sql.Timestamp日期:

Timestamp jdbcDatetime = new Timestamp(javaDatetime.getTime());

时间戳jdbcDatetime =新的时间戳(javaDatetime.getTime()));

I then need to do the reverse and convert the MySQL date & time into the corresponding Java representation

然后我需要做相反的操作，将MySQL的日期和时间转换为相应的Java表示

Do the reverse path:

做相反的路径:

• java.sql.Timestamp -> java.util.Date:
• java.sql。时间戳- > java.util.Date:

Date javaDatetime = new java.util.Date(jdbcDatetime.getTime());

Date javaDatetime = new java.util.Date(jdbcDatetime.getTime()));

• java.lang.Date -> java.util.String:
• . lang。- > java.util.String日期:

String stringDatetime = new SimpleDateFormat("yyyy.MM.dd hh:mm:ss").format(javaDatetime);

String stringDatetime =新的SimpleDateFormat(“yyyy.MM”)。dd hh:mm:ss).format(javaDatetime);

I've been concise. You have to pay attention about the use of SimpleDateFormat (e.g. cache an instance which has been initialized with applyPattern and setLenient).

我一直在简洁。您必须注意SimpleDateFormat的使用(例如，使用applyPattern和setLenient初始化的缓存实例)。

#2

In your case, the mysql type would be DATETIME and the Java 8 type would be LocalDateTime.

在您的示例中，mysql类型是DATETIME, Java 8类型是LocalDateTime。

Conversion between String and LocalDateTime

DateTimeFormatter fmt = DateTimeFormatter.ofPattern("yyyy.MM.dd hh:mm:ss");

LocalDateTime dateTime = LocalDateTime.parse(dateAsString, fmt);
String dateAsString = dateTime.format(fmt);

Interaction with the database (JDBC 4.2 compliant driver)

If the driver is compliant with JDBC 4.2 (the latest version of mysql Java connector should be compliant), you can do:

如果驱动程序兼容JDBC 4.2(最新版本的mysql Java connector应该兼容)，您可以做到:

LocalDateTime dateTime = rs.getObject(1, LocalDateTime.class);
preparedStatement.setObject(1, dateTime);

Non compliant driver

If your driver is not compliant yet, you would store/retrieve the DATETIME field as a java.sql.Timestamp like you did before Java 8.

如果您的驱动程序还不兼容，您可以将DATETIME字段存储/检索为java.sql。时间戳，就像Java 8之前那样。

You can then convert to/from LocalDateTime with:

然后可以使用:

//from LocalDateTime to Timestamp:
java.time.LocalDateTime dateTime = LocalDateTime.parse(dateAsString, fmt);
java.sql.Timestamp ts = Timestamp.valueOf(dateTime);

//from Timestamp to LocalDateTime:
java.sql.Timestamp ts = resultSet.getTimestamp();
java.time.LocalDateTime dateTime = ts.toLocalDateTime();

#3

You can format your code by this way:

你可以这样格式化你的程式码:

    SimpleDateFormat DATE_FORMAT = new SimpleDateFormat("dd/MM/yyyy");
    java.util.Date dateUtil = DATE_FORMAT.parse(dateEcheance);
    java.sql.Date dateSql = new java.sql.Date(dateUtil.getTime());

I hope that you will help you.

我希望你能帮助你。