java时间的数据类型,最适合存储日期和时间的SQL和Java数据类型

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

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

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

Thanks.

解决方案

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

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

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

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.String -> java.util.Date:

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

java.util.Date -> java.sql.Timestamp:

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

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

Do the reverse path:

java.sql.Timestamp -> java.util.Date:

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

java.lang.Date -> java.util.String:

String stringDatetime = new 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).

Update for Java 8

Some enhancements have been done on jdbc types (see JDBC 4.2) that simplify conversions. For the case illustrated above you can use toLocalDateTime and valueOf to convert from java.sql.Timestamp to the new java.time.LocalDateTime and back.