As a person who has had to fix timezone issues, ALWAYS store the data in UTC. Translate it to the timezone they selected for display only.
If SQL Server allows you to store the UTC offset as part of the datetime, then it's not as big of a deal. However, in MySQL, the date is just stored absent of timezone context. Here's a case in point, which I hope I can make to make sense :)
The user saves a time 2:00 EST for the day DST is removed. Then, let's say you store it in Central time, so it gets stored as 1:00 CST (but the CST part is not stored in MySQL). When the code pulls it out as 1:00am, it just assumes DST is still active because it hasn't been forced back. Then, for display, you change it back to the user's timezone, which bumps it ahead one hour to 2am EDT. However, at 2am EDT, the timezone gets bumped back to 1am EST. So, the user entered 2am EST but resulted in 1am EST on the way out.
I am trying to fix some legacy code with exactly this problem. In our logs, because of this issue, when we try to graph out that timeframe, we end up with 3 hours of 1am-2am instead of 2 for the transition and the 2am - 3am time is completely lost for eastern timezone. Problems only occur twice a year, and the transition away from DST is the worst...but it never would have been a problem if the times had been stored in UTC since UTC does not have any time changes, ever.
This may not be a problem you will encounter in your use-case, but, IMHO, it is best to just always store in UTC and translate on the view, as this has no drawbacks that I am aware of for any kind of application that has to deal with multiple timezones already.
One small exception, however...let's say that you are storing a recurring event. It happens at 18:00 Eastern on Tuesdays all year. For such an example, if you stored it as 23:00 UTC...part of the year, the event would run at 19:00. So, for such an example, you just want to deal with the timepiece separate from any date context.
[UPDATE] PHP timezone parsing
$dataFromDb = $row; // get it from DB and assume DB is in UTC $myDateTime = new DateTime($row['dateTime'], new DateTimeZone('UTC')); // convert it to desired timezone $myDateTime->setTimezone( new DateTimeZone('America/Chicago') ); echo $myDateTime->format('c'); //format in ISO-8601 format (very transportable)
You can then send the ISO format to JavaScript. However, JavaScript will take whatever time you send it and display it in the user's LOCAL timezone. So, if you want to display Eastern time even to someone in Central time, you'll need to force it into the Eastern timezone in JS. This is a PAIN without momentjs. So, if you don't want it to display the time in the user's local time, and you don't want to add momentjs to your project, then it is MUCH easier to just handle all the conversions in PHP and send the string as you want it formatted from PHP and don't let JS mess with it.
To Save to DB, same process:
//get selected timezone and date/time from user: $dateTime = new DateTime( $_POST['dateTime'], new DateTimeZone($_POST['timezone'])); $dateTime->setTimezone( new DateTimeZone('UTC') ); $dbColumn = $dateTime->format('Y-m-d H:i:s');
NOTE: I'm not doing validation in the above for simplicity and assuming the DateTime gets created correctly...don't actually trust that data from user will actually become a valid date.
