Handling MySQL datetimes and timestamps in Java

JavaMysqlDateJdbcTimestamp

Java Problem Overview


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 Solutions


Solution 1 - 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.

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.

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.

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

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.

Solution 2 - Java

The MySQL documentation has 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:

http://dev.mysql.com/doc/refman/5.1/en/datetime.html

http://www.coderanch.com/t/304851/JDBC/java/Java-date-MySQL-date-conversion

https://stackoverflow.com/questions/2400955/how-to-store-java-date-to-mysql-datetime

http://www.velocityreviews.com/forums/t599436-the-best-practice-to-deal-with-datetime-in-mysql-using-jdbc.html

EDIT:

As others have indicated, the suggestion of using strings may lead to issues.

Solution 3 - Java

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.

Best practice is to always store date-time in UTC timezone in DB. Sql timestamp type does not have timezone info.

When writing datetime value to 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,

  1. Read it as it is in java.sql.Timestamp type.

  2. Decorate the DateTime value as time in UTC timezone using atZone method in LocalDateTime class.

  3. 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"));
    

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionJames P.View Question on Stackoverflow
Solution 1 - JavaBalusCView Answer on Stackoverflow
Solution 2 - JavaGarettView Answer on Stackoverflow
Solution 3 - JavaDorjeeView Answer on Stackoverflow