Lost in dates and timezones

Question

I'm working on an application that stores conferences with their start and end date. Up until now, I was developing in Belgium and my server is in France, so everything is in the same timezone, no problem. But today, I'm in San Francisco, my server is in France and I noticed I have a bug.

I'm setting dates from a Flex client (ActionScript automatically adapts date display according to client local timezone, which is GMT-8 for me today. My server runs on Hibernate and MySQL in France (GMT+1). So when I look at my database using phpMyAdmin, I see a date set to "2010-06-07 00:00:01" but in my Flex client it displays "2010-06-06 15:00:01".

Ultimately, what I want is that the dates are displayed in the local timezone of the event, which is the date I set it to. So when I'm in Belgium and I set the start date of an event to be "2010-06-07 00:00:01" I want to retrieve it that way.

But I'm lost as to what layer adapts what. Is timezone stored in DATETIME MySQL columns (I can't see it in MySQL)? Does Hibernate to anything to it when it transfers it to java.lang.Date that has Timezone information? And ultimately, what is the best way to solve this mess?

Answer 1

It's truly a mess. I've writen something about it before.

In your case, I'd advise: each conference has (conceptually) a start-time of type "LOCAL DATETIME", and also has a timezone (the timezone can either be implicit in its location, or stored explicitly along the other data). That is conceptually correct: if the user says "The conference starts at 11:00 am", he means "at the moment at which the clocks it that city marks 11:00 am" (even if tomorrow the goverment decides to change the country GMT offset). It's also relatively simple to implement, as Java and Mysql are not very clever about timezones. Just stick (in your scenario= to this interpretation: Datetimes for conferences events are LOCAL, they do not represent a point in time in the physical continuum line of time, but a civic concept. (Think of it as -almost- a string). The drawback, of course, is that it will more difficult to do time calculations, specially inside the DB.

You'll only need to convert it to a "physical time" if you need to compute, for example, the interval from now to that event (if you need to rise an alarm, or distinguish events in the past from events in the future, etc). Because "now" is a physical time concept. In that case, you must do the conversion yourself. If you need to make that calculation inside the DB, you'll probably have to store the GMT-offset (say, in minutes) in the DB explicitly.

Answer 2

I found the problem. MySQL's DATETIME doesn't store any timezone information but java.util.Date does. So when Date was sent to my Flex client through an AMF channel, Actionscript applied a timezone offset. I solved the problem by replacing Date-typed fields by integers (date, month, year) and rebuilding actionscript Dates on the client side when needed.

Answer 3

I suggest you store and use all times in GMT, ie. GMT+0. This is the way system clocks are used in many system.

Only when you display the time, do you need to convert the time to the local timezone which is something you only need to do on the GUI. This keeps your servers timezone free.