Take the 2-minute tour ×

I've had this question for a long time. The problem is this: most SQL servers I've worked with (namely MySQL) don't store timezone information with dates, so I assume they simply store dates as relative to the server's local timezone. This creates an interesting problem in the case where I'll have to migrate servers to different timezones, or if I create a cluster with servers spread out over different datacenters, or if I need to properly translate date/times into local values.

For example, if I persist a date like 2011-08-12 12:00:00 GMT-7, it is persisted as 2011-08-12 12:00:00. If I have an event which happens across the world at a specific time, I have to assume that my server is storing dates in GMT-0700, (let's not even add daylight savings time into the mix) then translate them into dates depending on each user's local timezone. If I have multiple servers storing dates in their own timezones, all of this fails miserably.

For frameworks like Hibernate and Django, how do they deal with this problem, if at all? Am I missing something, or is this a significant problem?

share|improve this question

3 Answers 3

up vote 4 down vote accepted

As I see it, the best choices are:

  • Convert all times to UTC when storing them in the database, and localize them from UTC for display
  • Store the UTC offset in minutes (at least one modern time zone is a multiple of ten minutes offset from UTC) in a separate column from the date/time
  • Store the timestamp as a string

In my current project we encountered this issue (we use Postgres) and decided to store all times in UTC and convert as needed in the application. No separate storage of the time zone for us. We also decided that all client-server interaction using timestamps would be in UTC, and that local time zones would ONLY be considered for user interaction. This has worked out well so far.

Since you tagged this question with Django, I'll add that the pytz module is extremely useful for dealing with locale timezone conversion.

share|improve this answer
This makes the most sense. I thought that databases would automagically deal with stuff like this, but hey, it's alright. I'll probably just store everything in UTC and translate based on client time. How can I force Django to save in UTC? I have quite a few fields which use auto_now and auto_now_add, which might complicate things. –  Naftuli Tzvi Kay Aug 12 '11 at 21:06
You'll need to use pytz to do the conversions, and then use Django to store the "naked" timestamp. Because time zones are such a messy and political concept, there's a bit of learning curve, but honestly the API is pretty good and it covers all the corner cases. –  wberry Aug 12 '11 at 21:10
So I shouldn't rely on auto_now and auto_now_add, but should rather just set these things manually? –  Naftuli Tzvi Kay Aug 12 '11 at 21:18
Hmm, the Django documentation doesn't say if it uses utcnow() or a locale-based now() or the database NOW() function. If it's a UTC time, then you're good. If it's the database's NOW() function, then all you have to do is set the database to use UTC. But if it's getting a local time, based on the locale of the user that started the server, then that's an exposure because even a simple restart could suddenly change that time zone. So in that third case I would advise against auto_now. –  wberry Aug 12 '11 at 21:28

Your answer for MySQL lies on this page MySQL Server Time Zone Support

Basically MySQL offers automatic timezone support for any fields that use UTC (timestamp) field but not for fields that don't (date, time, and datetime fields). For UTC fields you can set the timezone from the client using SET time_zone = timezone;. For nonUTC fields you must calculate it yourself.

share|improve this answer
So should I simply store every datetime and date object as UTC, to keep things consistent? –  Naftuli Tzvi Kay Aug 12 '11 at 21:02
Yes since timezones are stored as offsets from UTC. –  Justin Lucas Aug 12 '11 at 21:05

You are so right I've often run into this and tend to look the TZ up and store it in a static "VARS" table so at least I can move it later.

Side notes:

  • Interesting the DATETIME manual doesn't even mention it
  • It is affected by NOW() and CURTIME() as shown here


share|improve this answer

Your Answer


