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.
About these ads

  • My Open Source

  • Twitter Updates

    Error: Twitter did not respond. Please wait a few minutes and refresh this page.

  • Enter your email address to follow this blog and receive notifications of new posts by email.

    Join 9 other followers

  • Flickr Photos

    Apertura Agile Open Spain 2011 - 32

    Apertura Agile Open Spain 2011 - 31

    Apertura Agile Open Spain 2011 - 30

    More Photos

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: