Keith Devens .com

How to handle international dates and times in PHP and MySQL

This is an update to my earlier post about storing times in MySQL.

1. Always store GMT

In your database tables you should always store the GMT/UTC (for the rest of this post I'll use "GMT") time, and you'll probably want to store the local time as well. For example, in my weblog entries table I have the following fields: Creation_DT, Creation_DT_GMT, Modification_DT, and Modification_DT_GMT. Those fields should be MySQL's DateTime field type. A scheme where you store the GMT time and some kind of time zone offset is not worth the trouble for the few bytes you'll save.

2. Don't depend on MySQL's timezone handling

Don't depend at all on MySQL's timezone handling. Depend completely on PHP's instead. Both PHP and MySQL respect the 'TZ' environment variable. However, while MySQL only pays attention to the TZ value when it starts up, PHP nicely lets you set it upon each request. Your operating system (unless it's Windows, I'm not sure what Windows does) most likely uses the tz database as its source for time zone data. Here's a handy reference card of all possible time zone values that should be valid for the TZ environment variable.

This means that you should never use MySQL's FROM_UNIXTIME or UNIX_TIMESTAMP functions because those depend on the time zone setting of your machine at the time MySQL started. Instead, always communicate with MySQL in the date format it expects, "YYYY-MM-DD HH:MM:SS" and have your code worry about what time zone those dates are in.

To set the TZ environment variable in PHP, use something like:


<?php putenv("TZ=US/Eastern");?>

That setting only lasts for the request, so don't worry about setting anything permanently.

Update: Here's documentation for Windows, and here are two pages with example settings. It appears they overlap, but it's not clear that not all time zone settings available on Unix are available on Windows.

3. Don't bother using PHP's gm* functions

Don't bother using PHP's gm* date functions. There's no need to. In your PHP code, make sure the TZ environment variable is set to the time zone you want and use date() for dealing with the request-specific localtime and then do date math using information you can get from date() (such as the 'Z' flag which gets you the time zone offset in seconds from GMT).

4. Utility Functions:

I have the following utility functions I use to do my all date handling:


<?php
function getGmt($time){return $time-date('Z',$time);}
function getGmtDiff($lt, $ut){return ($lt-$ut) / 3600;}
function getMysqlDate($timestamp){return date("Y-m-d", $timestamp);}
function getMysqlDatetime($timestamp){return date("Y-m-d H:i:s", $timestamp);}
function getTimestamp($mysql_datetime){return strtotime($mysql_datetime);}
?>

I hope it's fairly obvious what each function does. To show you how to use them, here's a snippet from the SQL I use to insert a weblog entry into my MySQL table:

INSERT INTO Weblog_Entries(... Creation_DT, Creation_DT_GMT ...) VALUES(

...'".getMysqlDatetime($t)."', '".getMysqlDatetime(getGmt($t))."',...)

Importantly, when you get the data out of the database, you don't need to do another conversion to or from GMT. However, as long as you only need to deal with dates in this epoch (1970-2038) you should always deal with dates and times as timestamps (32 bit integers) in your code. In other words, when you get your dates out of the database, convert them to timestamps as soon as possible using getTimestamp(). In my "weblog model" I do something like the following:


<?php
for($n=0,$c=count($entries); $n<$c; $n++){
    $entries[$n]['cd'] = getTimestamp($entries[$n]['cd']);
    $entries[$n]['cd_gmt'] = getTimestamp($entries[$n]['cd_gmt']);
    # ... and so on
?>

getGmtDiff() gives you the difference in hours between GMT and PHP's localtime. You can see that in use directly below as the timestamp for this post will display "(utc-4)" as its time zone. getGmtDiff() is how I derive that -4. Here's the code I use to print that:


(utc<?php printf("%+d",getGmtDiff($cd, $cd_gmt))?>)

That doesn't account for if the difference contains a fraction of an hour, but that's ok for me for now.

5. How time() works in PHP

It's important to understand how PHP handles timestamps. PHP always uses the GMT time for its timestamps. In other words, when you say:


<?php $time = time()?>

The number stored in $time is the seconds since Jan 1, 1970 GMT. The other functions such as date() then adjust for your local time zone on each call. The only difference between date() and gmdate() is that gmdate() doesn't do the adjustment for your time zone.

Anyway, I hope this all helps save people time.

Update: I experienced some problems with this scheme when my server was in the middle of changing for daylight savings time. When I figure out the right way to fix this I'll make a note of it here.

Permalink | 19 Comments | Tags: Programming, This website | id 5038

19:38 (utc-4)
edited: 2004-11-15 06:11 (utc-5)

Comments

afaik windows doesnt use the tz database
i tried extracting the tz/dst info out of windows xp and the zoneinfo files from a few months ago and oh boy were they inaccurate (specially windows')

the reference card is what (unforunately) you have to go by, that and a few non-authorative (but very uptodate) time related websites.

im a little confused by why your taking such a complicated (and cpu expensive) approach in storing gmt time.

why not simply fetch the current time in gmt, store that in a mysql datetime field. then when fetching the page u can use unix_timestamp() on the datetime field, it will extract seconds since epoch without modifying the datetime. (its actually faster cause it just fetches the integer as it stores it, instead of converting it to the 'yyyy-mm-dd hh:mm:ss' format)

then you can localize the time to whatver the user's timezone is (if you got user accounts, or simply wish to impose your own timezone) by adding the seconds offset from gmt and then compensating for daylight saving time. (hopefully not using the operating system's own tz/dst offset data)

also,
because your fetching and manipulating all the datetime info in the form of an integer, all this is stupidly fast (non-formating mysql fetch, tz gmt offset addition, dst compensation addition) right up until the final date() format command is called to produce a human readable datetime

the actual implementation of this is much shorter than its description

great article keith, wish u posted this months ago!

why not simply fetch the current time in gmt, store that in a mysql datetime field. then when fetching the page u can use unix_timestamp() on the datetime field, it will extract seconds since epoch without modifying the datetime.

That's wrong. Like I mentioned above, when you use UNIX_TIMESTAMP(), MySQL converts the time according to what time zone it thinks it's in. Your statement is only accurate when MySQL is operating in GMT time.

its actually faster cause it just fetches the integer as it stores it

Do you know for a fact that that's how MySQL stores its datetime format? Can you state with certainty that it is faster (have you benchmarked it)? In any case, formatting the datetime in YYYY-MM-DD HH:MM:SS format is certainly not one of the more computationally intensive things MySQL has to do in filling your query.

then you can localize the time to whatver the user's timezone is (if you got user accounts, or simply wish to impose your own timezone) by adding the seconds offset from gmt and then compensating for daylight saving time.

Yes. The only thing you seem to be arguing with me on is that you should have MySQL do the conversion from its internal datetime format into seconds-since-the-epoch instead of having PHP convert it. Like I've argued, that's not a workable solution.

The alternative is to store your time in MySQL as a GMT timestamp and store your localtime either as another timestamp or an offset in seconds from GMT to indicate your local time zone. Whether either solution will work for you depends on the needs of your application. For my application, in this case, my weblog, I need to be able to group by the day. For that to be possible having stored the datetime as an integer I'd need to use the div function, but that's only available as of MySQL 4.1, so I abandoned the idea.

because your fetching and manipulating all the datetime info in the form of an integer, all this is stupidly fast

MySQL can deal with the time in its internal format until right before it returns the query results to the client, at which time it will convert its datetime format to YYYY-MM-DD HH:MM:SS. Again, the only thing you seem to be arguing is that you should allow MySQL to do the conversion from its internal datetime format to a timestamp, but I've pointed out that that's unworkable.

This is unclear to me. I similarly decided to use a native Mysql timestamp for everything, which ends up with all my times in GMT. Now when reporting on those transactions, I am doing a conversion. such as DATE_ADD in SQL.

Because there is no simple way to determine if a date falls within Dayliht Savings time or not, I have read this article and am modifying the application accordingly. I am adding a "localtime" column to each table so that I may retain the system time in GMT and the local time in reference to the transaction.

Generating that second, localtime date is where I am hung up. My question is this; when I do this:

putenv('TZ=EST5EDT');

Does this provide a different offset depending on whether Daylight Savings Time is in effect or not? In other words, do I have to have some other logic that changes the argument I'm providing to putenv? I am thinking I do, because the following is a test I wrote and the results:

//_______________________

$modt = date('y-m-d h:m:s', time());
print "$modt";

05-04-27 09:04:39

putenv('TZ=EST');
$modt = date('y-m-d h:m:s', time());
print "$modt";

05-04-27 04:04:39

putenv('TZ=EST5EDT');
$modt = date('y-m-d h:m:s', time());
print "$modt";

05-04-27 05:04:39

I like your idea of doing the conversions in code instead of in the database.

As far as the problem you had during the DST change, I wonder what happened? Want to elaborate?

I think I see a possible problem with storing only UTC times or UTC times and localtimes in the database.

Converting localtimes to UTC times is straightforward at the time of storing the time in the database. However, there is the possibility of future timezone changes changing the localtime -> UTC mapping and thereby invalidating timestamps that were stored using previous rules. For example, let's say that we're writing calendaring software and back in 2006, someone in New York made an appointment for March 11, 2007 at 3am:

2007-03-11 03:00:00 US/Eastern
or in ISO 8601:
2007-03-11T03:00:00-05:00:00

In GMT, according to the known rules at the time, this would be:

2007-03-11 08:00:00 GMT
or
2007-03-11T08:00:00Z

Or maybe it's stored in epoch time, but I don't think that makes any difference for the matter at hand.

Now fast forward to 2007, the United States Congress has passed the recent 2007 DST change, moving the DST change up to March 11, and we diligently download and install the new tzdata package on our servers.

Our user looks up their appointment so we retrieve the stored GMT time from the database:

2007-03-11 08:00:00 GMT
or
2007-03-11T08:00:00Z

Using PHP and the latest tzdata, we convert this to a localtime for display to the user and get:

2007-03-11 04:00:00 US/Eastern
or
2007-03-11T04:00:00-04:00:00

Ooops. The user's appointment is now at 4am US/Eastern instead of 3am. I suppose if you used a stored localtime, you'd be OK, but what if the user has moved timezones since then? Or what if you're doing date arithmetic on the value - generally for date arithmetic it's easier and more efficient to operate solely on GMT times.

There is a way to detect this problem. We store with the UTC timezone the timezone name and the offset that we used to compute UTC. Thus, our 3am US/Eastern time would be stored internally as:

("2007-03-11 08:00:00 US/GMT", "US/Eastern", "-05:00")

representing that at that point in time US/Eastern was 5 hours behind GMT.

Now in 2007, we'd pull this out of the database and ask the updated tz database what the offset is for "US/Eastern". It replies with an offset of "-04:00".

Since the current offset of "-04:00" is different from the stored offset of "-05:00", we know that our stored time has to be adjusted back by 1 hour in order to reproduce its 2006 meaning.

One approach therefore is to store the UTC time, timezone name, and offset. Then after updating your tzdata, you run a little job that goes through your database and adjusts the stored UTC time wherever it finds a discrepancy between the stored offset and the current offset for the timezone in question.

Yeah, it's a pain, but I can't think of a better way thus far.

Having just been through this on a project, this is the scheme I came up with:

All date processing is done in GMT. To achieve this, I use time() in PHP or now() in MySQL to get the current time. MySQL has to be told to use GMT ('set time_zone="+00:00"'). If I need to convert a PHP timestamp to a string for internal use (i.e., to pass to MySQL) I use date_default_timezone_set ("UTC") to tell PHP to produce GMT-formatted strings. I don't need to do this very much, though, as I generally do most date handling at the mysql end.

Dates are stored and retrieved from MySQL as datetime fields in GMT. In PHP, I append " +00:00" to the date string after retrieval so that I know it's a GMT one, and pass it around in this format.

For display, I parse a 'yyyy-mm-dd hh:mm:ss +00:00' formatted string to a timestamp (using strtotime), get the correct display timezone from user's profile if one is logged in, or defaulting to Europe/London because that's where my site is based, and set it as the PHP current time zone (date_default_timezone_set), then display the date using 'strftime'. If I want to use a local time string, I omit the '+00:00' from the end and the same display code works fine.

For most applications, I don't think storing local time is a necessity. I do take Marc's point about future dates though. That's a caveat I wouldn't have expected (living, as I do, in a country that hasn't changed its DST rules since before I was born) and which would have seriously thrown me had I produced such an application.

Wednesday, July 28, 2004

How to handle international dates and times in PHP and MySQL

Comments

Recent comments