MySQL Bugs: #2876: Zeroed time using setDate()

Bug #2876	Zeroed time using setDate()
Submitted:	18 Feb 2004 13:30	Modified:	18 Feb 2004 13:51
Reporter:	[ name withheld ]	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	3.0	OS:
Assigned to:	Mark Matthews	CPU Architecture:	Any

Description:
I have a table with a DATETIME column.  (Not sure if this matters).

How to repeat:
I have some code to update it that looks (roughly) like the following:

  ps = connection.prepareStatement("insert into foo values (?)");
  ps.setDate(1, new java.sql.Date(logtime));
  ps.executeUpdate();

The problem is that the value written to the db has no time set.
That is, the date is correct but the time is set to midnight.

mysql> select * from foo;
+---------------------+
| issued_on           |
+---------------------+
| 2004-02-18 00:00:00 |
| 2004-02-18 00:00:00 |
| 2004-02-18 16:08:27 |
+---------------------+
2 rows in set (0.00 sec)

However the following works as expected:
  ps.setTimestamp(1, new java.sql.Timestamp(logtime));

[Sorry - clicked submit too soon]

The last row in the above example table is the result of using setTimestamp()

The JDBC spec states that java.sql.Dates have _no_ time component, and must have the hour/minute set to midnight and 0 minutes:

(from http://java.sun.com/j2se/1.4.2/docs/api/java/sql/Date.html):

"A thin wrapper around a millisecond value that allows JDBC to identify this as an SQL DATE value. A milliseconds value represents the number of milliseconds that have passed since January 1, 1970 00:00:00.000 GMT.

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."

Ah, didn't know that. Sorry and thanks.