Handling MySQL datetimes and timestamps in Java

by

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.

More Related Contents:

  1. Hours and seconds are lost when storing a date in the database [closed]
  2. ‘0000-00-00 00:00:00’ can not be represented as java.sql.Timestamp error
  3. How to convert TimeStamp to Date in Java?
  4. SQL syntax error in "insert into 'keys'(a,b,c,d,e) values(?,?,?,?,?)" [closed]
  5. com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
  6. Solving a “communications link failure” with JDBC and MySQL [duplicate]
  7. Java: Insert multiple rows into MySQL with PreparedStatement
  8. Cannot issue data manipulation statements with executeQuery()
  9. MySQL and JDBC with rewriteBatchedStatements=true
  10. Closing JDBC Connections in Pool
  11. No suitable driver found for ‘jdbc:mysql://localhost:3306/mysql [duplicate]
  12. com.mysql.jdbc.exceptions.jdbc4.CommunicationsException:Communications link failure [duplicate]
  13. java.util.Date format SSSSSS: if not microseconds what are the last 3 digits?
  14. Returning a ResultSet
  15. Java: Convert String to TimeStamp
  16. handling DATETIME values 0000-00-00 00:00:00 in JDBC
  17. What is the most recommended way to store time in PostgreSQL using Java?
  18. Retrieve an Image stored as BLOB on a MYSQL DB
  19. Can I execute multiple queries separated by semicolon with MySQL Connector/J? [duplicate]
  20. Timezones in SQL DATE vs java.sql.Date
  21. runtime error: java.lang.ClassNotFoundException: com.mysql.jdbc.Driver
  22. mysql prepared statement error: MySQLSyntaxErrorException
  23. SQL Error: 0, SQLState: 08S01 Communications link failure [duplicate]
  24. MySQL connector error “The server time zone value Central European Time” [duplicate]
  25. How to correctly write UTF-8 strings into MySQL through JDBC interface
  26. MySQL & Java – Get id of the last inserted value (JDBC) [duplicate]
  27. Performance of MySQL Insert statements in Java: Batch mode prepared statements vs single insert with multiple values
  28. PreparedStatement and setTimestamp in oracle jdbc
  29. java.sql.SQLException: No database selected – why?
  30. JDBC connection error : unrecognized timezone [duplicate]

Leave a Comment