PHP, MySQL and Time Zones
I am trying to integrate a timezone system in my app, i've really tried hard on avoiding making timezone-aware apps upto now - but its a mandatory requirement now so got no choice. TimeZones it just goes over my head. I've read several topics on PHP.net and also other sites including but not limited to SO. But i never could get the hang of it.
So i was wondering if some one can help me out here :( What i'm looking to make is a preference option in my app to allow users to choose their own timezones from a select menu but the app should also be able to SET/Choose the DST accordingly itself for each user.
Please i'm sure this will help others who are still striving to get the hang of the timezones, so please provide as much detailed explanation as possible, even if you have to consider me a complete dumbo/noob.
Edit for bounty:
I am adding a bounty to this question because I really thing we need a good canonical question about time zones when writing PHP/MySQL apps (thus I'm also adding the MySQL tag). I have found things from many places, but it would be good to have it all together. Charles' answer is great, but I still feel it's lacking somewhat. Here are some things I thought of:
- How to store the times in the database from a PHP
DateTime
object - Should they be stored in
DATETIME
orTIMESTAMP
? What are the benefits or caveats for each? - Do we ever need to worry about time zones with MySQL
DATE
? - How to insert values using
NOW()
. Do these need to be converted somehow either before or after the insert? - Is it necessary to set the time zone used by MySQL? If so, how? Should it be done persistently or upon every HTTP request? Does it have to be set to UTC or can it be anything else? Or is the server's time sufficient?
- How to retrieve values from MySQL and convert them to a
DateTime
object. Will putting it straight intoDateTime::__construct()
suffice or do we need to useDateTime::createFromFormat()
? - When to convert to local time and why. Is there ever a time that we would want to convert it before it is echoed back to the user (e.g. to compare to another DateTime object or a static value)?
- Is there ever a time we need to worry about Daylight Savings Time (DST)? Why or why not?
- What should someone do if they have previously inserted data (e.g. using
NOW()
) without worrying about the time zone to make sure everything stays consistent? - Anything else you think of that someone should look out for
If possible, try to separate it into logical sections to make it easier for future users to find the information. Be sure to provide code examples where necessary.
Solution 1:
This answer has been updated to accomodate the bounty. The original, unedited answer is below the line.
Almost all of the question points added by the bounty owner are in relation to how MySQL and PHP datetimes should interact, in the context of timezones.
MySQL still has pathetic timezone support, which means that the intelligence has to be PHP-side.
- Set your MySQL connection timezone to UTC as documented in the link above. This will cause all datetimes handled by MySQL, including
NOW()
, to be handled sanely. -
Always use
DATETIME
, never useTIMESTAMP
unless you very expressly require the special behavior in aTIMESTAMP
. This is less painful than it used to be.- It's ok to store the Unix epoch time as an integer if you have to, such as for legacy purposes. The epoch is UTC.
- MySQL's preferred datetime format is created using the PHP date format string
Y-m-d H:i:s
- Convert all PHP datetimes to UTC when storing them in MySQL, which is a trivial thing as outlined below
- Datetimes returned from MySQL can be handed safely to the PHP DateTime constructor. Be sure to pass in a UTC timezone as well!
- Convert the PHP DateTime to the user's local timezone on echo, no sooner. Thankfully DateTime comparison and math against other DateTimes will take into account the timezone that each is in.
- You're still up to the whims of the DST database provided with PHP. Keep your PHP and OS patches up to date! Keep MySQL in the blissful state of UTC to remove one potential DST annoyance.
That addresses most of the points.
The last thing is a doozy:
- What should someone do if they have previously inserted data (e.g. using
NOW()
) without worrying about the time zone to make sure everything stays consistent?
This is a real annoyance. One of the other answers pointed out MySQL's CONVERT_TZ
, though I'd personally have done it by hopping between server-native and UTC timezones during selects and updates, 'cause I'm hardcore like that.
the app should also be able to SET/Choose the DST accordingly itself for each user.
You don't need to and should not do this in the modern era.
Modern versions of PHP have the DateTimeZone class, which includes the ability to list named timezones. Named timezones allow the user to select their actual location, and have the system automatically determine their DST rules based on that location.
You can combine DateTimeZone with DateTime for some simple but powerful functionality. You can simply store and use all of your timestamps in UTC by default, and convert them to the user's timezone on display.
// UTC default
date_default_timezone_set('UTC');
// Note the lack of time zone specified with this timestamp.
$nowish = new DateTime('2011-04-23 21:44:00');
echo $nowish->format('Y-m-d H:i:s'); // 2011-04-23 21:44:00
// Let's pretend we're on the US west coast.
// This will be PDT right now, UTC-7
$la = new DateTimeZone('America/Los_Angeles');
// Update the DateTime's timezone...
$nowish->setTimeZone($la);
// and show the result
echo $nowish->format('Y-m-d H:i:s'); // 2011-04-23 14:44:00
By using this technique, the system will automatically select the correct DST settings for the user, without asking the user whether or not they're currently in DST.
You can use a similar method to render the select menu. You can continually reassign the time zone for the single DateTime object. For example, this code will list the zones and their current times, at this moment:
$dt = new DateTime('now', new DateTimeZone('UTC'));
foreach(DateTimeZone::listIdentifiers() as $tz) {
$dt->setTimeZone(new DateTimeZone($tz));
echo $tz, ': ', $dt->format('Y-m-d H:i:s'), "\n";
}
You can greatly simplify the selection process by using some client-side magic. Javascript has a spotty but functional Date class, with a standard method to get the UTC offset in minutes. You can use this to help narrow down the list of likely timezones, under the blind assumption that the user's clock is right.
Let's compare this method to doing it yourself. You'd need to actually perform date math every single time you manipulate a datetime, in addition to pushing a choice off on the user that they aren't going to really care about. This isn't just sub-optimal, it's bat-guano insane. Forcing users to signify when they want DST support is asking for trouble and confusion.
Further, if you wanted to use the modern PHP DateTime and DateTimeZone framework for this, you'd need to use deprecated Etc/GMT...
timezone strings instead of named timezones. These zone names may be removed from future PHP versions, so it'd be unwise to do that. I say all of this from experience.
tl;dr: Use the modern toolset, spare yourself the horrors of date math. Present the user with a list of named time zones. Store your dates in UTC, which won't be impacted by DST in any way. Convert datetimes to the user's selected named time zone on display, not earlier.
As requested, here's a loop over the available time zones displaying their GMT offset in minutes. I selected minutes here to demonstrate an unfortunate fact: not all offsets are in whole hours! Some actually switch half an hour ahead during DST instead of a whole hour. The resulting offset in minutes should match that of Javascript's Date.getTimezoneOffset
.
$utc = new DateTimeZone('UTC');
$dt = new DateTime('now', $utc);
foreach(DateTimeZone::listIdentifiers() as $tz) {
$local = new DateTimeZone($tz);
$dt->setTimeZone($local);
$offset = $local->getOffset($dt); // Yeah, really.
echo $tz, ': ',
$dt->format('Y-m-d H:i:s'),
', offset = ',
($offset / 60),
" minutes\n";
}
Solution 2:
-
How to store the times in the database from a PHP
DateTime
objectThe SQL-92 standard specified that temporal literals should be passed in SQL using a suitable data-type keyword (e.g.
TIMESTAMP
for date/time values) followed by a string representation of the value (containing an optional timezone offset if non-default).Sadly, MySQL is not compliant with this part of the SQL standard. As documented under Date and Time Literals:
Standard SQL permits temporal literals to be specified using a type keyword and a string.
[ deletia ]
MySQL recognizes those constructions and also the corresponding ODBC syntax:
[ deletia ]
However, MySQL ignores the type keyword and each of the preceding constructions produces the string value
'str'
, with a type ofVARCHAR
.The documentation goes on to describe the literal formats which MySQL supports and, notably, explicit timezone offsets are absent. There is a feature request to fix this that is now over seven years old and which does not look likely to be introduced any time soon.
Instead, one must set the session's
time_zone
variable prior to exchanging date/time values between server and client. Therefore, using PDO:-
Connect to MySQL:
$dbh = new PDO("mysql:dbname=$dbname", $username, $password); $dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, FALSE);
-
Set the session
time_zone
to that of theDateTime
object:$qry = $dbh->prepare('SET SESSION time_zone = ?'); $qry->execute([$datetime->format('P')]);
-
Produce a suitable literal from the
DateTime
object and pass to MySQL as normal (i.e. as a parameter to a prepared statement).As described in the documentation, there are a number of possible literal formats that one can use. However, I'd suggest using a string in
'YYYY-MM-DD hh:mm:ss.ffffff'
format (note that fractional seconds will be ignored in versions of MySQL prior to 5.6), as it is the closest to the SQL standard; indeed one could prefix the literal with theTIMESTAMP
keyword to ensure that one's SQL is portable:$qry = $dbh->prepare(' UPDATE my_table SET the_time = TIMESTAMP ? WHERE ... '); $qry->execute([$datetime->format('Y-m-d H:i:s.u')]);
-
-
Should they be stored in
DATETIME
orTIMESTAMP
? What are the benefits or caveats for each?PHP
DateTime
objects should always be stored inTIMESTAMP
type columns.The most fundamental difference is that
TIMESTAMP
stores timezone information (by storing the value in UTC and converting to/from as required by thetime_zone
variable above), whereasDATETIME
does not. ThusTIMESTAMP
is useful for representing a specific moment in time (analogous to PHPDateTime
objects), whereasDATETIME
is useful for representing the time that is seen on a calendar/clock (as in a photo).As documented under The
DATE
,DATETIME
, andTIMESTAMP
Types:The
DATETIME
type is used for values that contain both date and time parts. MySQL retrieves and displaysDATETIME
values in'YYYY-MM-DD HH:MM:SS'
format. The supported range is'1000-01-01 00:00:00'
to'9999-12-31 23:59:59'
.The
TIMESTAMP
data type is used for values that contain both date and time parts.TIMESTAMP
has a range of'1970-01-01 00:00:01'
UTC to'2038-01-19 03:14:07'
UTC.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 asDATETIME
.) By default, the current time zone for each connection is the server's time. The time zone can be set on a per-connection basis. As long as the time zone setting remains constant, you get back the same value you store. If you store aTIMESTAMP
value, and then change the time zone and retrieve the value, the retrieved value is different from the value you stored. This occurs because the same time zone was not used for conversion in both directions. The current time zone is available as the value of thetime_zone
system variable. For more information, see Section 10.6, “MySQL Server Time Zone Support”.The
TIMESTAMP
data type offers automatic initialization and updating to the current date and time. For more information, see Section 11.3.5, “Automatic Initialization and Updating forTIMESTAMP
”.Note the final paragraph, which often catches out newcomers to MySQL.
It may also be worth adding that, as documented under Data Type Storage Requirements,
DATETIME
values require 8 bytes for storage whereasTIMESTAMP
values only require 4 bytes (the underlying data storage format can be found in Date and Time Data Type Representation). -
Do we ever need to worry about time zones with MySQL
DATE
?It is only meaningful for a time to be sensitive to timezone. By definition, a date alone is universally the same irrespective of one's timezone and therefore there is no need to "worry about time zones" when using MySQL's
DATE
data type.The corollary to this is that, if one has a value that is sensitive to timezone, one must also store its time e.g. in a
TIMESTAMP
column: using aDATE
column causes irreversible loss of significant information. -
How to insert values using
NOW()
. Do these need to be converted somehow either before or after the insert?As documented under
NOW()
:Returns the current date and time as a value in
'YYYY-MM-DD HH:MM:SS'
orYYYYMMDDHHMMSS.uuuuuu
format, depending on whether the function is used in a string or numeric context. The value is expressed in the current time zone.Since "the value is expressed in the current time zone" and that same "current time zone" will be used in evaluating date/time values, one does not have to worry about time zone when using MySQL's
NOW()
function (or any of its aliases). Therefore, to insert a record:INSERT INTO my_table (the_time) VALUES (NOW());
Note that, as mentioned above, MySQL's automatic initialisation of
TIMESTAMP
columns makes redundant most attempts to useNOW()
during record insertion/update. -
Is it necessary to set the time zone used by MySQL? If so, how? Should it be done persistently or upon every HTTP request? Does it have to be set to UTC or can it be anything else? Or is the server's time sufficient?
This is already addressed above. One can set MySQL's
time_zone
variable globally, if so desired and thus avoid having to set it upon every connection. See MySQL Server Time Zone Support for more information. -
How to retrieve values from MySQL and convert them to a
DateTime
object. Will putting it straight intoDateTime::__construct()
suffice or do we need to useDateTime::createFromFormat()
?As documented under Compound Formats, one of the date/time formats recognised by the parser that PHP uses in
DateTime::__construct()
is MySQL's output format.However, since the MySQL output format does not include the timezone, one must be sure to furnish the
DateTime
constructor with that information through its optional second argument:$qry = $dbh->prepare('SET SESSION time_zone = ?'); $qry->execute([$timezone->getName()]); $qry = $dbh->query('SELECT the_time FROM my_table'); $datetime = new DateTime($qry->fetchColumn(), $timezone);
Alternatively, one can have MySQL convert the time to a UNIX timestamp and construct the
DateTime
object from that:$qry = $dbh->query('SELECT UNIX_TIMESTAMP(the_time) FROM my_table'); $datetime = new DateTime($qry->fetchColumn());
-
When to convert to local time and why. Is there ever a time that we would want to convert it before it is echoed back to the user (e.g. to compare to another DateTime object or a static value)?
I'm not sure what you mean by "local time" (local to whom? the RDBMS? the webserver? the webclient?), but comparisons between
DateTime
objects will handle timezone conversions as necessary (PHP stores the values internally in UTC and only converts for output). -
Is there ever a time we need to worry about Daylight Savings Time (DST)? Why or why not?
Generally speaking, if you follow the methodology given above, the only concern for DST is when ensuring that values are rendered to the user in the timezone that they expect.
-
What should someone do if they have previously inserted data (e.g. using
NOW()
) without worrying about the time zone to make sure everything stays consistent?As mentioned above, use of
NOW()
should never cause problems.If literal values have been inserted into a
TIMESTAMP
column whilst the session'stime_zone
variable was set to an incorrect value, one will need to update those values accordingly. MySQL'sCONVERT_TZ()
function may prove helpful:UPDATE my_table SET the_time = CONVERT_TZ(the_time, '+00:00', '+10:00');