java.sql.Date is not a real date

Thursday, 14 August 2003

java.sql.Date stores only date information, not times. Simply converting a java.util.Date into a java.sql.Date will silently set the time to midnight. So, to store date/times to be manipulated as java.util.Date objects, don’t do this:

// BUG: loses time of day
preparedStatement.setDate(1, new java.sql.Date(date.getTime()));

do this instead:

preparedStatement.setTimestamp(1, new java.sql.Timestamp(date.getTime()));

java.sql.Timestamp extends java.util.Date, but it should not be used as a Date. In JDK 1.3.1, Timestamp.getTime() (inherited from Date) returns the time to the nearest second only, but JDK 1.4.2 and JDK 1.5 it returns the time to the nearest millisecond as expected. So in JDK 1.3, when reading a timestamp from a ResultSet, don’t do this:

// Java 1.3
java.util.Date d = resultSet.getTimestamp(1);
long millis = d.getTime(); // BUG: loses fractional seconds in JDK 1.3

To get the full date including milliseconds, you have to do this:

java.sql.Timestamp timestamp = resultSet.getTimestamp(1);
java.util.Date d = new java.util.Date(timestamp.getTime() +
                                      timestamp.getNanos() / 1000000);

In JDK 1.4.2 and JDK 1.5, you can just do this, depending on what you’re going to do with the Date:

// Java 1.4+
java.util.Date d = resultSet.getTimestamp(1);

But this might be safer since it avoids any other potential Timestamp problems:

// Java 1.4+
java.util.Date d = new java.util.Date(resultSet.getTimestamp(1).getTime());

If your code needs to run on JDK 1.3 and later, you’ll have to do this:

java.sql.Timestamp timestamp = resultSet.getTimestamp(1);
long millis = (timestamp.getTime() / 1000) * 1000 + timestamp.getNanos() / 1000000;
java.util.Date d = new java.util.Date(millis);

For more information, see the Javadoc for java.sql.Timestamp.
JDK 1.3.1 TimeStamp Javadoc
JDK 1.4.2 TimeStamp Javadoc
JDK 1.5.0 TimeStamp Javadoc

Note the subtle difference between 1.3.1 and the later ones: The following lines appear in the 1.3.1 Javadoc, but were removed in subsequent version.

The getTime method will return only integral seconds. If a time value that includes the fractional seconds is desired, you must convert nanos to milliseconds (nanos/1000000) and add this to the getTime value.

See also the amusing notes on Bug 4679060 on developers.sun.com.

Tags:

36 comments

You can leave a comment, or trackback from your own site.

  1. Randomiser, The “m” in your format string means minute, not month. Try using “M”.

  2. Great! I swore for a while trying to get this done.

    Thanks!

  3. Hi,
    I’ve read most of your script and it has been
    helpful to me, I need a help. I an new in coding and I have been given an assignment to develop a loan application software with Netbeans 6.0 using java codes please I need your help.

  4. Hi,
    I want to store a datetime in entity(of JPA)(via controller processing).In entity date is simply
    in Date(date in Oracle). But i want to store date with time. Date becomes from JSF form via entity bean(in request scope).What should I do??

  5. 爱到发烧阿什发

  6. very nice info indeed, thx for writing this one out!

  7. Joshua Chung

    What about in 1.6?

    in NetBeans 6.7.1 / Java SE 6:

    connection.preparedStatement.setDate(2, new java.util.Date(resultSet.getTimestamp(1).getTime()));

    and it does not compile and strike through the word “Date” in java.util.Date .

    Any Suggestion?

  8. @Joshua, PreparedStatement.setDate() takes a java.sql.Date, not a java.util.Date.

  9. Parijat Bansal

    thanks! it helped me

  10. Hi..
    How to remove the time component as well from the date portion

  11. @Nagaraj I’m sure you can figure it out. You can do it with the Calendar class.

Leave a comment