0

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!

SeaBass
  • 1,584
  • 4
  • 20
  • 46
  • 2
    Possible duplicate of [PHP, MySQL and Time Zones](http://stackoverflow.com/questions/5768380/php-mysql-and-time-zones) and [Daylight saving time and time zone best practices](http://stackoverflow.com/questions/2532729/daylight-saving-time-and-time-zone-best-practices?rq=1) – gp_sflover Apr 26 '17 at 18:37

1 Answers1

1

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.

tadman
  • 208,517
  • 23
  • 234
  • 262
  • @tadman Thanks! Would it be an option to keep it in my timezone instead and have that be the master instead of UTC? I like being able to view directly in the database and have all the times being where I am. But if there are any drawbacks by doing that I'll skip that convenience. – SeaBass Apr 26 '17 at 19:55
  • 1
    One skill you'll want to invest in is being able to read UTC times and make sense of them. There's nothing more annoying than moving your database from a server in California to one in New Jersey and suddenly all your times are totally busted because of a time-zone shift. Keep it neutral and you won't have problems like that. Pilots use "Zulu time" (UTC) as a matter of principle, they're always changing time-zones, and so can you. – tadman Apr 26 '17 at 20:03