Bug #40279 Timestamp values get truncated when passed as prepared statement parameters
Submitted: 23 Oct 2008 10:03 Modified: 25 Oct 2012 23:28
Reporter: Domas Mituzas Email Updates:
Status: Closed Impact on me:
Category:Connector / J Severity:S3 (Non-critical)
Version:5.1 OS:Any
Assigned to: Alexander Soklakov CPU Architecture:Any

[23 Oct 2008 10:03] Domas Mituzas
If a timestamp value is passed via prepared statement parameters, sub-second precision is stripped even if underlying field (like VARCHAR(255)) would allow storing full value.

Though workaround exists ( ps.setString(1,ts.toString()) ), this needs additional work to be done. 

How to repeat:
String sql = "INSERT INTO t VALUES(?)";
Timestamp ts = new Timestamp(System.currentTimeMillis());
PreparedStatement ps = conn.prepareStatement(sql);
ps.setTimestamp(1, ts);

Suggested fix:
do not strip precision
[1 Aug 2009 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[10 Oct 2012 14:00] Alexander Soklakov
Bug#60584 is a duplicate of this report.
[25 Oct 2012 23:28] John Russell
Added to changelog for 5.1.23: 

If a timestamp value was passed through prepared statement
parameters, fractional-second precision was stripped off, even if the
underlying field (such as VARCHAR(255)) could store the full value. A
workaround was to convert the timestamp value to a string when
specifying the prepared statement argument, for example
[5 Aug 2015 17:23] Fabio Yamada
Hi, I'm using version 5.6.21-log MySQL Community Server and had the subsecond data truncated while using the preppared_stmt.setTimestamp(1, time_stamp) method. I had to use the workaround found in this bug report, preppared_stmt.setString(1,time_stamp.toString()), to get the subsecond part persisted in db.