Can JDBC + MySQL be convinced to ignore timezones?

Question

I've got a Java web application in which the client sends timestamped data to the server to be stored in a MySQL database and retrieved later. These timestamps should always be treated as local time and never adjusted for timezone: If a client in one timezone inputs a time of 18:00:00, it should display for a client in another timezone as 18:00:00.

The database column holding this value is of type DATETIME, as I was under the impression that it does no timezone-adjusting. The java application receives timestamps as java.util.Date objects, and converts them to java.sql.Timestamp objects before inserting them in PreparedStatements. Somewhere along the way however, the time values are getting offset when the client and server are in different time zones.

To give you an idea of what I'm looking for, I could store all my timestamps as strings and that would be exactly the effect I want, but it doesn't make sense to me to have to format and parse strings as dates when they need to be operated on.

What is the usual way to handle such applications when timestamp values should always be treated as local time?

Answer 1

The trouble is that java.util.Date represents a "point in time" not a time like 18:00. (Point in time, in this case, is measured by the number of milliseconds since 1st Jan 1970 00:00:00 GMT but in principle that doesn't matter here.)

The usual way (at least, the way I've always done it) is to store points in time in the database, so if you do something at 1pm your time, I see it as e.g. 3pm as that's the same point in time in my timezone. So the requirement to store a "time" (HH:MM) without storing a "point in time", i.e. without adjusting for the recipient's timezone, is quite unusual, at least in my experience.

I would use the DATETIME datatype as you're using, but insert and read the values as strings from Java i.e. with the setString method on the PreparedStatement and getString on the ResultSet.

Answer 2

MySQL, or any other standard database doesn't store the timezone information. It just stores the time offset from the Epoch. It is up to the the client to determine what the time is for a particular timezone.

Somewhere along the way however, the time values are getting offset when the client and server are in different time zones.

Of course, that's how it should work. If you want to force a mandatory timezone, you could apply the timezone offset which you want before you send the timestamp to the mysql database instance (you should use a standard library such as the dreaded built in jdk ones or joda time to do this instead of just adding or subtracting 1 hour).

Answer 3

Configure both the web application server and the database server to use a timezone of GMT/UTC. It boils down to that the entire code base and all layers should use the one and same timezone.

Related:

• Daylight saving time and timezone best practices

Answer 4

What I'd do is store the timestamp as a long, representing GMT time - that way, there's no way for MySQL to screw with it (and as a bonus, you'll get better precision - MySQL timestamps are only accurate to one second). Add a separate column to store the time zone, then convert it to a local time string before displaying to the user.

Also, beware of daylight-savings. Some places don't have it, and it doesn't always start and stop at the same time in all the places that do have it.

Answer 5

You should be able to force a particular TimeZone while reading and writing to the database through using the setTimestamp and getTimestamp methods that take a Calendar object.

Answer 6

This is an old thread, but no one posted a simple answer. Use a TIMESTAMP field instead. MySql does not do timezone-adjusting for TIMESTAMP, so it would just work out of the box for you.

Most similar threads are trying to work around this lack of adjustement, but for your case it plays well with the behavior you need.