MySQL Bugs: #3620: PreparedStatement.setTimestamp doesn't use Calendar argument

Bug #3620	PreparedStatement.setTimestamp doesn't use Calendar argument
Submitted:	1 May 2004 2:42	Modified:	23 Nov 2004 18:23
Reporter:	Karl Niemeier	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	mysql-connector-java-3.1-nightly-2004042	OS:	Windows (Windows XP Pro 2002)
Assigned to:	Mark Matthews	CPU Architecture:	Any

Description:
As I understand jdbc date processing, the overload of PreparedStatement.setTimestamp() that takes a Calendar should produce a different DATETIME in the database than the overload that doesn’t take a Calendar if the Calendar passed as an argument has a timezone different than the vm’s default timezone. Both Microsoft Sql Server and Oracle jdbc drivers both produce different DATETIME values when you use a Calendar vs. when you don’t.

Perhaps there is something about MySQL’s operation that I don’t understand but I don’t understand how PreparedStatement.setTimestamp(int, Timestamp, Calendar) is working properly when it seems to throw away the Calendar object (at least, when ConnectionProperties. getUseTimezone() is false).  

How to repeat:
create table test_table (
col_bigint int,
col_date timestamp);

// conn has already been set
    Timestamp ts = new Timestamp(System.currentTimeMillis());
    PreparedStatement pstmt = conn.prepareStatement("INSERT INTO test_table (col_bigint, col_date) VALUES (?, ?)");
    pstmt.setLong(1, 42L);
    pstmt.setTimestamp(2, ts);
    pstmt.execute();
    Calendar myCal = Calendar.getInstance(TimeZone.getTimeZone("UTC"));
    pstmt.setLong(1, 44L);
    pstmt.setTimestamp(2, ts, myCal);
    pstmt.execute();

The reason for this feature not being enabled unless you set 'useTimezone' to true is for legacy applications (that expect that the driver does nothing to the timezone, because MySQL doesn't actually store timezone information with a timestamp (yet)).

Does the driver exhibit the correct behavior in your testcase when 'useTimezone=true'? (it does in our testsuite).

For this discussion, let:
- the timezone of both the client vm and the MySQL server be PDT
- "ts" be a java.sql.Timestamp, whose value is the point-in-time (PiT) that equates to 10:00 PDT (and 12:00 Central DST and 17:00 UTC and ...) 
- let "cal" be a Calendar with timezone set to UTC

Here's what I get:
- setTimestamp(1, ts) puts "10:00" in database (this is true for MySQL, Sql Server, and Oracle)
- setTimestamp(1, ts, cal) puts "17:00" in database in Sql Server and Oracle*, which is the correct UTC time for this PiT
- setTimestamp(1, ts, cal) puts "10:00" in database in MySQL when useTimezone=false
- setTimestamp(1, ts, cal) puts "02:00" in database in MySQL when useTimezone=true

If I pull the value out of MySQL with a matching getTimestamp, i.e. one that uses a UTC Calendar, I do get back the PiT that I put in. So, I can see where this probably looks like it works for some Java code. However, someone using the mysql command line tool will see 02:00 even though the original PiT didn't match this time at either PDT or UTC. Additionally, java code that pulls this out of the database using getString() would get "02:00" also whereas getString() on Oracle or Sql Server would get "17:00", which is the UTC time for the PiT.

*Oracle example is using Oracle TIMESTAMP and not TIMESTAMP WITH TIMEZONE.

Fixed in 3.0.8.