Mysql date problem in different timezones

Question

Hi,

I have been facing this date issue since a long time. Have tried a lot of things but in vain. The problem goes like this:

The date entered by the user is '2009-08-12'. It gets stored properly in the db.

In the GMT+530 timezone the date gets displayed correctly as well. But when I change the timezone to GMT+1 the date gets displayed as '2009-08-11' i.e it displays the date as 1 day back.

Any help or inputs or suggestions would tremendously help.

Thanks

Nait

Answer 1

The date is probably stored as DATETIME or TIMESTAMP, 2009-08-12 00:00:00 +0530

Being converted into GMT+1, it gives you 2009-08-11 19:30:00 +0100

MySQL's DATETIME doesn't store timezone along with the timestamp, so if your type is DATETIME, it's probably your database access layer that converts the dates.

MySQL's TIMESTAMP type does store the times in UTC and they are converted back into the client's timezone when queried.

Answer 2

@Quassnoi has explained why this is happening, but the way to solve it is to use the various methods on java.sql.PreparedStatement and java.sql.ResultSet that take a calendar parameter - methods like java.sql.PreparedStatement.setDate(int, Date, Calendar), java.sql.ResultSet.getDate(int, Calendar), etc.

My advice would be to pick a standard timezone that will be used to represent dates in the database, typically UTC, then perform any conversion using the methods I mention above.

Of course if you can standardize your client on the same timezone as the database that's even better and it allows you to perform data comparisons without concern for timezones, leaving you to simply perform any conversion when a date is displayed.

Answer 3

This problem is very widely seen. For example, here are some remarks by Martin Fowler.

The underlying issue is that Java's date classes treat dates/times like quantities in physics - representations of a specific, immutable instant in time.

The problem is that this does NOT match how most people think casually about time. In typical discourse, people think of '2009-08-01 18:00' as fixed idea. The problem is that there is no time zone attached to such a representation; hence, it does not actually represent a specific instant.

This is a continuing source of confusion in many applications. While Java always uses timezones (either implicitly or explicitly), databases don't usually store time zone information along with dates/times. Hence, Java must ADD an assumed time zone when it creates a Date object. This will be done by a JDBC driver when it converts the value to a Date object. If such code runs on a server, then the assumed time zone often doesn't match that used by the client/end user.

The only answer is to make sure your Java code uses a 'standard' time zone.