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 Open Source

  • Twitter Updates

    • @micho Infovia anyone? 2 hours ago
    • After days in Elixir, I write Ruby like: class Scoped.Thing do def baz do do_something() end end And wonder why it doesn’t parse 7 hours ago
    • RT @islomar: A piece of advice for anyone applying for a job: clearly transmit your MOTIVATIONS, GOALS and WHYS, not just a bunch of topics… 11 hours ago
    • RT @realntl: @wycats We want to say "sorry mgmt, we made a mess and need time to rewrite" but "the lang/framework sucks and we need to migr… 21 hours ago
    • @MarkVillacampa Al menos con todo esto, alguna gente aprenderá a actualizarse y por qué. Sobre todo la oposición! 21 hours ago
  • Enter your email address to follow this blog and receive notifications of new posts by email.

    Join 9 other followers

  • Flickr Photos

%d bloggers like this: