Dealing With Date And Times And Multiple TimeZones

[JustinK101]

I am building an application that people will use in many different timezones. Furthermore people will be pulling data stored from users in different timezones. For example a user might enter data and a datetime in Pacific Time. Then another user might pull that data entered in Pacific Time from Eastern Time. The second user should see the date as entered by the orginal user in Pacific Time. 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(), universal time, 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. Also, if a user changes timezones, when they pull historic data it will show their updated timezone, not the orginal datetime that the record was inserted.

 

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.

 

The big problem 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. Big problem with method 2, is that I need another field for each datetime which stores the timezone that datetime was stored in.

 

Finally how do I deal with daylight savings time, which in itself is a bloody headache.

 

Thanks for the help.

 

Thanks for the help.