Remember: Date + Time = Timestamp

This is mostly a reminder for myself (I read somewhere that one needs to write down things to actually make them stick to the brain). But it might also help you.

When reading date-time information from a database (that is, year, day, month… but also hour, minute, second, millisecond), always use ResultSet.getTimestamp(). I repeat. Always.

By some reason, I keep using getDate(). Maybe because a java.sql.Date can store information about time, but the javadoc forbids it:

To conform with the definition of SQL DATE, the millisecond values wrapped by a java.sql.Date instance must be ‘normalized’ by setting the hours, minutes, seconds, and milliseconds to zero in the particular time zone with which the instance is associated.

To make things worse, some JDBC drivers ignore the javadoc, and will set the date with the time portion when available. An example of this is Oracle 10 jdbc driver. Interestingly, Oracle 9 enforces javadoc behaviour.

So, if you are running away from version 10 and come to version 9 (like I did), be very careful. If you see that suddenly your dates are truncated at midnight, loosing their time component, then your are not properly reading your ResultSet.

By the way… the link to the javadoc is to the getTimestamp(String, Calendar) version on purpose.

It is well known that using column names is generally better than using an index (you don’t care how the select is written, don’t forget to increment counter, the readability of your code is improved…).

But I think that far less people know about the Calendar parameter. It is very useful when your database stores dates for a given (known) timezone, and your application runs in a different one. Passing the right calendar will adjust the date with the time difference and daylight savings (if needed).

So, key points to remember:

  • resultset.getDate() reads (sometimes) only date information
  • resultset.getTimestamp() reads (always) date and time information
  • Don’t assume that changing a JDBC driver will keep your application working flawlessly.

  • My profile

    View Abel Muño's profile on LinkedIn

    amuino on Twitter

  • My projects

    The cloud market:
    Find the virtual image you need on the cloud.
  • Top Posts

  • Archives

  • Categories

%d bloggers like this: