Timezones in MySQL and PHP
20th July, 2007 |
10 Comments
Timezones in web applications are often dealt with the same way as character sets. Either incorrectly or ignored. The case of unicode is improving slowly as developers discover the need for international language support.
The problem with not handling timezones correctly is not apparent in the usual case of the webserver and the database server being in the same timezone. A lot of PHP code passes the task of timestamps on to MySQL, e.g. by calling NOW() on inserting or updating records. It is nessesary to know in what timezone the timestamps are stored to process them correctly.
Often a specific timezone is assumed for displaying date and time. This could very well be incorrect for a large number of visitors. If the database server is in yet another timezone, things begin to get out of sync.
MySQL's handling of timezones is - obviously - separate from that of PHP. Along with the fact that the MySQL timezone can only be changed by users with SUPER privileges, this means that MySQL cannot be relied on for creation of timestamps in correct timezone. In other words, the usual call to NOW() is not an option.
PHP 5.1 introduced the date_default_timezone_set() method for setting the timezone PHP will use for functions like date() (or alternatively the date.timezone php.ini directive). This allows for a flexible and consistent way of creating timestamps in the correct timezone form within PHP. One user in London might start a thread while a response comes from New York. To handle this consistently and make sure both users see local time and the correct timespan between the posts, use date_default_timezone_set() to create the timestamps from PHP in a fixed timezone like GMT.
Time for an example to illustrate the practical use of this. I created a simple class Timer for dealing with these operations. The methods will be called statically as there is no need to instantiate the class.
class Timer
{
static function DatetimeInGMT() {
return date("Y-m-d H:i:s", time()-date("Z",time()));
}
static function GMTDatetimeToLocal($datetime) {
$time = strtotime($datetime);
return date("Y-m-d H:i:s", $time+date("Z",$time));
}
}
Timer::DatetimeInGMT() produces a timestamp in GMT. I use it instead of MySQL's NOW() because it calculates the time relative to the timezone set in PHP rather than that of MySQL.
When reading the timestamp back in to PHP, another function call is needed to convert it back to whatever timezone PHP is in at that time (it might be a different user viewing with another timezone setting). That's the job of Timer::GMTDatetimeToLocal($datetime).
These two very simple functions and a correct setting of PHP's timezone will ensure that dates are stored in a consistent way and displayed correctly.
« Mocking Surface
– The PHP version in Mac OS X is old »
The best way to handle timestamps is to always use real Unix timestamps. They inherently have no time zone (i.e. they are all Zulu time). In mysql store them as INT. When all of your timestamps are universally based on the Unix epoch then the only concern is displaying them as you wish. They'll always be accurate and the time zone is simply not an issue, even if you move servers.
INT is best, but has some limitations (range, precision). http://en.wikipedia.org/wiki/Year_2038_problem (comming soon :)
Really nice tutorial, thanks a bunch
It doesn't work for me...it constantly displays 1:00:00 and the next day
@Matt, Joakim - Unix time is not perfectly unambiguous as it cannot handle leap seconds.
A minor point of no importance to some, but is extremely important in some situations.
A useful article nonetheless, thank you :-)
Matt, the difference between a Unix timestamp and a easily readable representation like a datetime is really just a matter of formatting. A date itself carries no information about timezone in itself, which is why we have standards like RFC822 that append timezone information.
How to store the datetime is a matter of preference and range. The MySQL datetime type supports dates between '1000-01-01 00:00:00' and '9999-12-31 23:59:59' whereas 32 bit implementations of Unix time ends in 2038, as Foo mentions.
The point is to be sure that the stored date and time is in a known, consistent timezone so that valid conversions to other timezones can be made at any point onward.
To set the MySQL timezone to UTC without privilege:
SET SESSION time_zone = '+0:00'
to read the timezone which currently applies to NOW(), FROM_UNIXTIME(), UNIX_TIMESTAMP(), and the implicit conversion done when reading and writing columns of the TIMESTAMP type:
SELECT @@session.time_zone
do not use the magic timezone name 'SYSTEM' anywhere, because if you have used 'SET SESSION time_zone' then you are not operating under 'SYSTEM' timezone. For example CONVERT_TZ(, 'SYSTEM', @@session.time_zone) is no longer a no-op in that case.
to write a UNIX timestamp into a DATETIME column in UTC, regardless of the session timezone:
CONVERT_TZ(FROM_UNIXTIME(%ld), @@session.time_zone, '+0:00') (**note)
to write the same to a TIMESTAMP column, instead of a DATETIME column:
FROM_UNIXTIME(%ld)
to write the present time to a DATETIME column in UTC, regardless of the session timezone:
CONVERT_TZ(NOW(), @@session.time_zone, '+0:00') (**note)
to write the same to a TIMESTAMP column:
NOW()
to read a UNIX timestamp out of a DATETIME column stored in UTC:
UNIX_TIMESTAMP(CONVERT_TZ(column, '+0:00', @@session.time_zone)) (**note)
to read a UNIX timestamp out of a TIMESTAMP column:
UNIX_TIMESTAMP(column)
to read a date and time, in UTC, pretty-printed by MySQL, out of a DATETIME column stored in UTC:
SELECT column
to read the same date out of a TIMESTAMP column:
SET SESSION time_zone = '+0:00'
SELECT column
it is not possible to use CONVERT_TZ to read a TIMESTAMP column. You _have_ to set the session time_zone instead. You'd think CONVERT_TZ would work, but AFAICT passing @@session.time_zone as an argument to CONVERT_TZ works on INSERT but does not work on SELECT.
summary:
If you don't want to set the MySQL process's timezone to UTC (even just for the session), then this should be safe and correct:
* use only TIMESTAMP columns
* move all data in and out of the database exclusively using FROM_UNIXTIME() and UNIX_TIMESTAMP() coercion
If you care about leap seconds, and understand leap seconds and the difference between UTC, TAI, and GMT:
http://en.wikipedia.org/wiki/UNIX_time#Non-synchronous_Network_Time_Protocol-based_variant
and for some reason believe that MySQL will actually store leap seconds in a column, calculate intervals including leap seconds magically solving the impossiblity of doing this six months into the future, and you believe NOW() will sometimes return 23:59:60, and in spite of the lack of documentation about all this you believe the whole thing will cooperate with your overall application to handle leap seconds (i.e., you never ask the kernel for the present time with a syscall instead you SELECT NOW(), or if you ask the kernel you ask with ntp_gettime() and pay attention to the return code not just the ntptimeval, and you have some magical library that takes (returncode, ntptimeval) and translates it into SQL time or user-presentable time, and you think it will work to roll your clock back to before a leap second to test this whole monster (I don't), and you are loading fresh leap second data into both MySQL and your Unix (returncode, ntptimeval) pretty-printing library at least twice a year, then:
* you must set the session time_zone to UTC.
* once you've done that, you must read/write the database with SQL time representation, never using FROM_UNIXTIME() or UNIX_TIMESTAMP().
* you should probably use TIMESTAMP columns, but DATETIME columns should also work if the time_zone stays UTC. If you touch the database from outside your app, for example you try to put NOW() into such a column from phpMyAdmin with time_zone local, that will be wrong if you used DATETIME and right if you use TIMESTAMP.
If you want to use DATETIME columns without setting the session time_zone,
* change your mind. There is no way to make a DATETIME column unambiguously represent the hour between 1 and 2am each fall in timezones that have summer time. This is (**note).
* If you choose to have a DATETIME column but store it as UTC instead of storing local time like MySQL expects you to be doing, then you must accomplish this using 'SET SESSION time_zone' to avoid the summertime problem above. If you use CONVERT_TZ anywhere to accomplish this goal, you will have the summer time 1 - 2am ambiguity problem.
* It is not an issue of ``using the same timezone everywhere.'' It is not merely a matter of the database being tied to a particular local timezone without having that local timezone stored inside the database, so problems arise from the inelegance of the database on its own not fully representing your data until you unlock it with the secret key---knowledge of the local timezone. And it's not a matter of avoiding messy implicit conversions---passing local time on the interface between application and MySQL when both app and MySQL are dealing internally in UTC, which can go awry every time the timezone rules change if the querying application and MySQL itself do not have the same tzinfo dataset which is quite likely. EVEN IF ALL THESE THINGS WERE TAKEN CARE OF by some extremely meticulous person, you would STILL have the summertime ``fall back'' issue above. You simply cannot use DATETIME at all.
* Do not talk to me about leap seconds when there is one hour each year that you cannot handle at all.
Is there any difference between your first method DatetimeInGMT and simply doing a gmdate("Y-m-d H:i:s")? Not wanting to pick holes, it is a genuine question. The principle of subtracting the timezone offset is a good one and I appreciate the snippet. Very handy, thanks!
A -big- thanks to Miles for preventing me from having to stay up all night. His post is excellent and helped me fix a pesky bug with a mysql server. Thank you!
Post A Comment
You need JavaScript to post comments.