How To Insert DateTime Dealing With TimeZones

[JustinK101]

I am building an application that people will use in many different timezones. I am trying to determine which is the best way to store date and times in all my datetime columns in MySQL.

 

Method 1: Always insert and update with UTC_TIMESTAMP() from the query itself, so basially all dates stored in the database are all the same in relation to each other.

 

Method 2: Always insert with DATE_ADD(UTC_TIMESTAMP(), INTERVAL XX HOUR) where XX is the users hours offset from UTC Time.

 

The benefit of method 1, is that all dates are normalized, but the draw back is everytime you select a date you must always remember to offset with the users timezone.

 

The benefit of method 2 is that dates are already inserted in their final format and don't require offsetting when selected. The drawback is that the dates are not stored in the database normalized.

 

One issue I see with Method 1, what happens if a user updates their timezone in preferences? That in essence changes a report they would pull since the datetimes are offset on the fly depending on the users timezone at the time of the select versus the time inserted.

 

Thanks for the help.

[JustinK101]

Crap, just realized how do I deal with day light savings time with UTC? Usually in my previous projects I just used MySQL NOW() because all I cared about was local time. Multiple timezones add a serious level of complexity.