Bug #46099 useServerPrepStmts=true/false setDate() behaves differently
Submitted: 9 Jul 2009 20:41 Modified: 21 Dec 2015 16:02
Reporter: Dmitry Popov Email Updates:
Status: Can't repeat Impact on me:
None 
Category:Connector / J Severity:S2 (Serious)
Version:5.1.7 OS:Any
Assigned to: Filipe Silva CPU Architecture:Any

[9 Jul 2009 20:41] Dmitry Popov
Description:
When the connection property useLegacyDatetimeCode=false and useServerPrepStmts=false then setDate() shifting "day" part. The MySql server set to UTC and the client timezone is different. In case of useServerPrepStmts=true it doesn't happen. The corresponding column in DB has a DATE type. It doesn't happen with DATETIME columns. Actually in the case of useServerPrepStmts=false it makes timezone conversion but it doesn't do that if pstmts turned off.

How to repeat:

1.Have a server set to UTC and have a different timezone on client.
2.Create a table with DATE column.
3.Create a connection with useLegacyDatetimeCode=false and useServerPrepStmts=false.
4.Make an insert with timestamp for example 1247097600936 that is Wed Jul 08 2009 19:00:00 GMT-0500 (Central Daylight Time)
5.Result would be 2009-07-09
6.Create a connection with useLegacyDatetimeCode=false and useServerPrepStmts=true.
7.Make an insert
8.Result would be 2009-07-08

Suggested fix:
In case of useServerPrepStmts=true it uses ServerPreparedStatement.storeDateTime413AndNewer that has a switch by DATE type. So it doesn't convert values in UTC for DATE columns. 

But PreparedStatement make a conversion for DATE. See newSetDateInternal()

Implementations should be synced up.
[9 Jul 2009 20:43] Dmitry Popov
-
[10 Jul 2009 13:16] Tonci Grgin
Hi Dmitry and thanks for your report.

Verified by looking into code.
[10 Jul 2009 14:23] Dmitry Popov
Another issue found:

If pstmt enabled and the DATE is inserted using prepared statement but selected using regular statement then result will be different.
[21 Dec 2015 16:02] Filipe Silva
Since Connector/J version 5.1.35 there is a connection property 'noTimezoneConversionForDateType' which allows to turn on/off the time zone switching features for DATE type. This property has default value 'true' so no conversions should occur by default.

Same behavior was observed when using server prepared statements and client prepared statements.

This report will be closed as "Can't repeat". Please reopen it of file a new one if you find some situation were the problem persists.

Thank you,