StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POPHP, MySQL and Time Zones
text
Body
copied!<p>I am trying to integrate a timezone system in my app, i've really tried hard on avoiding making timezone-aware apps upto now - but its a mandatory requirement now so got no choice. TimeZones it just goes over my head. I've read several topics on PHP.net and also other sites including but not limited to SO. But i never could get the hang of it.</p> <p>So i was wondering if some one can help me out here :( What i'm looking to make is a preference option in my app to allow users to choose their own timezones from a select menu but the app should also be able to SET/Choose the DST accordingly itself for each user.</p> <p>Please i'm sure this will help others who are still striving to get the hang of the timezones, so please provide as much detailed explanation as possible, even if you have to consider me a complete dumbo/noob.</p> <hr> <p><strong>Edit for bounty:</strong></p> <p>I am adding a bounty to this question because I really thing we need a good canonical question about time zones when writing PHP/MySQL apps (thus I'm also adding the MySQL tag). I have found things from many places, but it would be good to have it all together. Charles' answer is great, but I still feel it's lacking somewhat. Here are some things I thought of:</p> <ul> <li>How to store the times in the database from a PHP <code>DateTime</code> object</li> <li>Should they be stored in <code>DATETIME</code> or <code>TIMESTAMP</code>? What are the benefits or caveats for each?</li> <li>Do we ever need to worry about time zones with MySQL <code>DATE</code>?</li> <li>How to insert values using <code>NOW()</code>. Do these need to be converted somehow either before or after the insert?</li> <li>Is it necessary to set the time zone used by MySQL? If so, how? Should it be done persistently or upon every HTTP request? Does it have to be set to UTC or can it be anything else? Or is the server's time sufficient?</li> <li>How to retrieve values from MySQL and convert them to a <code>DateTime</code> object. Will putting it straight into <code>DateTime::__construct()</code> suffice or do we need to use <code>DateTime::createFromFormat()</code>?</li> <li>When to convert to local time and why. Is there ever a time that we would want to convert it <em>before</em> it is echoed back to the user (e.g. to compare to another DateTime object or a static value)?</li> <li>Is there ever a time we need to worry about Daylight Savings Time (DST)? Why or why not?</li> <li>What should someone do if they have previously inserted data (e.g. using <code>NOW()</code>) without worrying about the time zone to make sure everything stays consistent?</li> <li>Anything else you think of that someone should look out for</li> </ul> <p>If possible, try to separate it into logical sections to make it easier for future users to find the information. Be sure to provide code examples where necessary.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload