Jump to content

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


TheFilmGod

Recommended Posts

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.