Most appropriate SQL and Java data types for storing date and time [duplicate]

Question

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.

Answer 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

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.

Answer 2

In your case, the mysql type would be DATETIME and the Java 8 type would be 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:

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.

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();