How should I store date/time objects in SQL?

Question

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?

Answer 1

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.

Answer 2

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.

Answer 3

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

HTH