CODEWITHSUNDEEP
phpmysqlapacheutc
GDP
GDP

Reputation: 8178

Web and Database server settings to get UTC timezone "right"

I'm overwhelmed trying to get this right: We've got servers located across a dozen time zones, with Apache and MySQL running on all or some of them, as well as MySQL hosted on Amazon RDS.

I want to know "Best Practices", or how to otherwise configure each MySQL and PHP installation so that when a row is added to the database from PHP I'm certain that the value there is actually the UTC time when the event happened, regardless of where the server is located when it happened. Presenting it to the user in any given timezone is not an issue - I just want to know that the datetime columns are actually storing the actual moment in time when something occurred.

As it is now, the Web Servers are set to whatever the local timezone is due to scheduled events, etc., and I'm not sure which parts of the puzzle use which settings from where to come up with whatever is written to the database.

I apologize if the question seems unclear, at this point I don't know what I don't know, so getting a precise question is even challenging. Also, all our dates are in the database as DateTime fields, so storing timestamps isn't possible.

Upvotes: 2

Views: 1468

Answers (2)

timdev
timdev

Reputation: 62864

If you restrict yourself to MySQLs DATE and DATETIME types, you can largely ignore time zone issues in MySQL itself. You want to avoid MySQL's TIMESTAMP type because:

MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME.) By default, the current time zone for each connection is the server's time.

https://dev.mysql.com/doc/refman/5.7/en/datetime.html

As far as reading/writing DATETIME values, you'll get back exactly what you put in, which is good.

That leaves you with the problem of always ensuring you are writing UTC values to the database.

The best way to ensure that PHP is using UTC is to explicitly set it in your application using date_default_timezone_set(). That will ensure that calls like date('Y-m-d H:i:s') will give you the UTC value. It will also ensure that something like (new \DateTime('now'))->getTimezone() will return a UTC \DateTimeZone instance.

You should note, of course, that things get significantly more difficult when you're storing date/time values that you take from users. In those cases, you'll need to somehow determine what timezone the user is in, and handle conversion to UTC before persisting the values. Assuming your users have some per-user timezone setting, you basically do something like:

/** @var \DateTimeZone $userTZ */
$userTz = getUserTimezone();
$dateTime = new \DateTime($user_submitted_date_string, $userTz);
$dateTime->setTimezone(new \DateTime('UTC'));
$dateTimeStr = $dateTime->format('Y-m-d H:i:s');

Upvotes: 2

e4c5
e4c5

Reputation: 53734

Quite simple, always store unix time in the database (or if you want microsecond accuracy with microtime ). Then regardless of the timezone of each of your webservers, if two of them recieve a request at the same time, it would be the same integer value that is saved in the database (the database field shoudl obviously be an int (or big int for micro time))

And how to display? Easy with javascript.

new Date(unix_timestamp);

This produces a date and time in the user's timezone.

Upvotes: 2

Related Questions