JDBC Prepared Statement . setDate(....) doesn't save the time, just the date.. How can I save the time as well?

Question

I have the following Query :

INSERT INTO users (user_id, date_created) VALUES (?,?)

I have the following prepared statement

PreparedStatement insertUser = dbConnection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);

insertUser.setInt(1, 7);
java.util.Date now = new java.util.Date(System.currentTimeMillis());
insertUser.setDate(2, new java.sql.Date((new Date(System.currentTimeMillis())).getTime()));
insertUser.executeUpdate();

If I check the database, I find that it is inserting only today's date not the time though, so it would be : 2011-07-29 00:00:00

What should I put in the setDate() to get the time as well?

Answer 1

Instead of Date you should use a Timestamp and the setTimestamp method.

pratically you have to do something like that:

private static java.sql.Timestamp getCurrentTimeStamp() {
    java.util.Date today = new java.util.Date();
    return new java.sql.Timestamp(today.getTime());
}

....

preparedStatement.setTimestamp(4,getCurrentTimeStamp());

Answer 2

The Java type java.sql.Date will be normalized to represent only a SQL DATE and not an instant of Time. From the API documentation:

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.

The normalization explains why you are seeing 00:00:00 as the time.

If you want to retain the time information, consider using the Timestamp class that can represent both a Date and a Time.

Obviously, you should also use the corresponding SQL types to define your table structure; if you want to store timestamps in the database, use the SQL type preferred by your database. While some databases and their JDBC drivers might allow you to store timestamps in DATE columns, it is advisable to use the equivalent of the SQL TIMESTAMP type if it is available.