mysql - Best way to handle storing/displaying dates in different timezones in PHP?

Question

I've been reading up on this topic for the past few hours, and I think I've got a handle on it, but I'd like some confirmation.

Situation

I want a user in, say, California to be able to post a comment that will be stored in MySQL. I then want a user in, say, Texas to be able to view the comment with the post date adjusted to his or her time zone.

Proposed Solution

Storing

1. Run the following at the start of the application so that all date functions use UTC timezone: date_default_timezone_set('UTC');
2. $Date = new DateTime(); to get a DateTime object with the current date and time in UTC.
3. Use $Date->format() to get the value to insert into the datetime type column in MySQL.

Displaying

1. Get the user's timezone information from JavaScript and store it in a cookie.
2. Run a MySQL SELECT query to retrieve the datetime column value.
3. $Date = new DateTime($row['time']); to instantiate a DateTime object with the stored UTC time.
4. $Date->setTimezone(new DateTimeZone($userTimezone)); to adjust the UTC time to the user's timezone.
5. Display using $Date->format();

Is that the gist of what has to be done? Am I missing a better solution? Thanks for your help!

Answer 1

It can be made simpler still. Since you are using JavaScript then why not use JavaScript to adjust the time zone on the client as well?

1. Store all times on the server as UTC
2. Serve them to the client as UTC
3. Client uses JavaScript adjust time to local time zone

Not only does this make things simpler but it also overcomes a problem with your model. If I registered my account in New York but travelling to Australia, I want to see the times as per the Australian time zone. In fact, using JavaScript you use can easily adjust the settings, making the design even more dynamic. Second, you can avoid the overhead of storing the user's time zone.

That said, if you want your design to degrade to non-JavaScript browsers then you are better off taking a full server side approach relying on HTTP cookies (as opposed to relying on JS to fetch cookies).