CODEWITHSUNDEEP
phpmysqltimezone
SeaBass
SeaBass

Reputation: 1764

Timestamps / Time Zones / PHP / MySQL

I have my Apache server and PHP set to date_default_timezone_set("America/Los_Angeles").

In MySQL I save TIMESTAMP fields with CURRENT_TIMESTAMP and some of them EXTRA as "on update CURRENT_TIMESTAMP".

Am I doing this the right way or am I loosing the whole point of TIMESTAMPS? I want the user to be able to choose their own timezone but it seems like it's saving the local timezone to the MySQL instead of a universal reference point.

Should I instead set the server timezone to UTC and in PHP date_default_timezone_set("America/Los_Angeles") or whatever the user timezone is and then save the values to the MySQL fields with something else than CURRENT_TIMESTAMP (which seems to vary depending on the php setting)?

Thank you!

Upvotes: 0

Views: 78

Answers (1)

tadman
tadman

Reputation: 211580

Ideally you want your database to store data in UTC so that way it's not anchored to any particular geography. Incoming data from users is converted according to the user's time-zone, and anything you display can likewise be converted back to the user's preferred time-zone.

Some systems even go so far as to send UTC time over to the client in the HTML, but tag the element with something that JavaScript hooks on to and re-renders, client-side, with the appropriate local time.

It's best to have a user-specified setting that's persisted in their user record and/or session that defines what conversion, if any, should be done to the dates and times they're specifying or being shown.

It's also worth noting that TIMESTAMP fields are limited to the year 2038, so they're already living on borrowed time. It's best to use a more standard DATETIME field which has a much wider range of acceptable values.

Upvotes: 1

Related Questions