Handling MySQL datetimes and timestamps in Java
date, java, jdbc, mysql, timestamp
Solution
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.
Problem
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?