1

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?

Sebastien
  • 3,583
  • 4
  • 43
  • 82
  • This is not clear for me: "if what I want is that the dates are displayed in the local timezone of the event, which is the date I set it to." Who is "I" ? The user ? The conferences happen all in a fixed known timezone or it depends on the location of the conference (which is set by... the user?) – leonbloy Jun 05 '10 at 00:10
  • `java.util.Date` doesn't have timezone information. It's always GMT (Epoch time). BTW: the time part seems to be irrelevant in your Flex application, why do you display it? – BalusC Jun 05 '10 at 00:13
  • Am I missing something? This issue would only exist if you use NOW()/CURRENT_TIMESTAMP/etc to populate the DATETIME. If a user provides a date & time, that should be written to the DB - timezone shouldn't come into effect. Providing notifications based on time in various timezones would be an issue however... – OMG Ponies Jun 05 '10 at 00:30
  • Let me clarify: in the Flex admin interface, the administrator sets the start date of an event to be on June 7th at 0:00:00:000 (implicitly local time of the event), and the user sees this exact same date and time, and not the date adapted to his own timezone. This is what I want. But right now, what I get is that the administrator sets the date to be June 7th, 0:00:00:000, and the user retrieves it as June 6th, 15:00:00:0000 @BalusC I don't display the time in my Flex app, but it's relevant as it determines the date, here June 6th instead of June 7th – Sebastien Jun 05 '10 at 00:36

3 Answers3

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.

Peter Lawrey
  • 525,659
  • 79
  • 751
  • 1,130
2

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.

Community
  • 1
  • 1
leonbloy
  • 73,180
  • 20
  • 142
  • 190
0

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.

Sebastien
  • 3,583
  • 4
  • 43
  • 82
  • No, `java.util.Date` does not store any timezone information. It's nothing more than a container for a number of milliseconds since 01-01-1970 00:00:00 UTC. A `java.util.Date` object represents an "absolute" moment in time, it doesn't by itself know anything about timezones. – Jesper Jun 05 '10 at 21:28