JDBC, MySQL, and Timezones

[CornedBee]

Hi,

I've got an enterprise application written in Java using the Spring framework, backed by a MySQL database, to which it connects using JDBC. For this application, I've got a large set of unit tests.

The application always worked fine at home (Central Europe), but right now I'm in the US and it just wouldn't work: operations dependent on a timestamp I sent along with them would somehow produce unexpected results. I've narrowed it down to a problem of timezones and conversion between them.

When you pass a Date object to a prepared statement as a DATETIME parameter, the driver is supposed to convert the time correctly. Of course, 'correctly' is open to interpretation. My original, apparently wrong assumption was that MySQL's DATETIME data type, not having any timezone info, is assumed to store GMT dates.
The driver does not assume this, though. It assumes it stores the dates in the DB server's local time zone. So while I used, in my Java code, a GregorianCalendar object, carefully initialized to GMT, to create the timestamps I sent to the server, the driver happily ... did something I don't quite understand with them. One one hand, it requires you to enable the conversion behaviour. Otherwise, apparently, it doesn't do anything. However, if I enable the behaviour, the first thing that happens is that it can't connect, because the server reports being in the time zone "EDT" (Easter Daylight Time), which has multiple mappings in Java. So I have to use the serverTimezone directive to override this behaviour.
That still doesn't work, though. No matter what I set the server timezone to, it always produces the same error.

Well, to make a long story short, I have to screw around a lot with the system to make it work, mostly trial and error. I don't like it. I'd like some definite way of making it work, without having a getGmtTimestamp() method use the supplied values as in CST.

Does anyone have experience with this sort of thing?

[ComputerJy]

I don't know if you tried that, but take a look at "java.text.SimpleDateFormat" class.
I hope it helps you in some way, but I'll keep looking for something better

EDIT: take a look at this tutorial

[CornedBee]

That might be a workaround (don't know if every driver accepts a string value for a DATETIME parameter), but it doesn't solve the underlying problem: that Java/MySQL behaves unpredictably when I supply a timestamp to PreparedStatement.setDate() or setTimestamp().

Of course, I already have a workaround in modifying the unit tests. I really don't want to modify the actual code.

[ComputerJy]

I respect that, but I think the problem is in the MySQL server not in your java code, try asking in the Database Development, you might get a better response

[CornedBee]

Could be. I'll ask there.