Time portion from your Date / DateTime / Timestamp field getting truncated when using the JDBC ResultSet?

Problem – You see that from Date-Time value you are saving into your database, the time part is getting truncated at some point. What on earth is happening?

You may notice that if you look at the field in your database, you do see the time being persisted. So infact, it is something you are doing wrong when retrieving the field from the DB.

The reason why this problem occurs is in part because of poor API design.

* What do you think this code does?
Date date = resultSet.getDate("my_date_field");

What doesn’t strike most developers right away is that resultSet.getDate returns a java.sql.Date. And that java.sql.Date extends java.util.Date!

By definition, a java.sql.Date has only Date – no time! But it is assignable to a java.util.Date which is the norm in typical application code. That’s where your time part probably gets eaten up due to an incorrect getter for the JDBC resultSet.

Similarly, java.sql.Time is returned by resultSet.getTime. It can be assigned to a java.sql.Date. In this case, it is the date part that tends to mysteriously disappear.

If you want to do this right –

  • Make sure you know what your DB uses for the column – a TIMESTAMP field or a DATETIME field or a DATE field.
  • Use the right getter from the ResultSet class – Date, Time and Timestamp are all compatible with java.util.Date but only Timestamp contains the date as well as the time.
  • Use the correct setter when saving data to your DB as well.

While we are on the topic of dealing with Date and Time in Java, have you considered ditching the primitive Java Date / Calendar API and adopting Joda-Time instead? If you are at an early stage in your project, that is something you definitely need to look at.

This entry was posted on Friday, May 27th, 2011 at 12:00 am and is filed under Uncategorized. You can follow any responses to this entry through the RSS 2.0 feed. Both comments and pings are currently closed.

One Response to “Time portion from your Date / DateTime / Timestamp field getting truncated when using the JDBC ResultSet?”

  1. Ilias Tsagklis

    Hi there,

    Interesting blog! Is there an email address I can contact you in private?