Timezones aren't "genius" when you start developing a web application

[TheFilmGod]

Until recently I thought timezones were simply genius. But recently I have realized that they make everything far more complicated than they should be when developing a web application.

 

The basic consensus is to:

 

Save all date/time information as UTC. Output the date/time information according to the user's timezone preference with a bit of help from php's awesome datetime class.

 

This sums up everything in a two sentences - HOWEVER there is more than meets the eye.

 

I'm working on a web application that will enable schools to create events and announcements on their "network" pages. Now you may want to jump to the gun and say store everything in MYSQL in UTC. But that may not be ideal.

 

Would it make sense if a person across the world came to the school webpage and saw that the dance was happening april 10th at 2:30 am? No it wouldn't. Shouldn't the school's events and announcements be saved in the school's local time? Does that make sense?

 

Students profile pages should on the other hand be stored in UTC. It would make interactions student to student difficult if the times would be localized.

 

Does my rational make sense? Or should I store EVERYTHING in UTC?

 

Last question (I promise) - should I entirely drop datetime as the preferred field type in mysql and go with timestamp. I can't imagine how much harder it would be query the database according to local settings by the datetime field.

[ignace]

I found this on MySQL manual

 

The current session time zone setting affects display and storage of time values that are zone-sensitive. This includes the values displayed by functions such as NOW() or CURTIME(), and values stored in and retrieved from TIMESTAMP columns. Values for TIMESTAMP columns are converted from the current time zone to UTC for storage, and from UTC to the current time zone for retrieval.

 

UTC is the way to go apparently. I also found:

 

Per-connection time zones. Each client that connects has its own time zone setting, given by the session time_zone variable. Initially, the session variable takes its value from the global time_zone variable, but the client can change its own time zone with this statement:

 

mysql> SET time_zone = timezone;

 

For more information http://dev.mysql.com/doc/refman/5.1/en/time-zone-support.html

[Daniel0]

Would it make sense if a person across the world came to the school webpage and saw that the dance was happening april 10th at 2:30 am? No it wouldn't. Shouldn't the school's events and announcements be saved in the school's local time? Does that make sense?

 

The display time should obviously default to the school's local timezone. If you want to be further unambiguous and target a worldwide audience, what's preventing you from printing the timezone information to the screen?