Best mysql db design to solve timezone issues I faced problem with timezone on my last two projects. So What is best way to sort out problems with timezone on db design.

I mean, What format should i added date time stamp.

On of my problem. My client asked me to expire particular event on users timezone. How can i solve this.

Thanks in advance.
Off the top of my head, the simplest thing I could think of would be to store three values:


UTC (DATETIME),
timezone offset for datetime in minutes (SMALLINT) (-1440 to 1440)
DST (daylight saving time), 0 or 1 (TINYINT)


I originally was thinking of timezone offset in hours. @yercube reminded me of other places with 15 min or 30 min additional offsets . For example, these cities have these times:


New York City at 4:00 PM
Bangalore 1:30 AM next day
Katmandhu 1:45 AM next day


You would have to


perform date arithmetic to handle display of datetimes
compare datetimes as pure UTC values