PHP/MySQL Time zone design pattern

Suppose you have a PHP/MySQL application that you want to adapt to work across multiple timezones. I.e. Users from Toronto should see all dates and times in Eastern time while Vancouver users should see times in Pacific time. On this surface this appears like a simple thing to do, but there are a number of pitfalls to watch out for.

For this design pattern we’ll assume that we can always obtain the user’s timezone by calling a function, say getUserTimezone() which would return something like Canada/Pacific.

The tricky part is making sure that different users can view and edit dates in their local timezone, while keeping the data consistent. For example, if Toronto Tony posts an event in our application, he can specify that it starts at 8pm (meaning 8pm Toronto time). When Vancouver Vinnie logs in, he sees this event, but the time says ‘5pm’ because he sees the event in Vancouver time.

Design Decisions

  1. How do we store the dates in the database? Do we store them in Toronto time? Vancouver time? Some other time? or does it matter?
  2. Both MySQL and PHP contain functions to convert dates between different timezones. Do we do the conversion in MySQL? In PHP? In both? Does it matter?

Our solution for the first design decision, is that it doesn’t matter how we store the dates in the database, as long as we are consistent. I.e. we must store ALL of the dates in Toronto time or ALL of the dates in Vancouver time, but we cannot store some in one timezone and some in another. When deciding on an appropriate default timezone for the database, I considered using GMT (Greenwich mean time) but it is probably better just to leave the server running the most intuitive time for the majority of users. If the server is physically located in Denver, why not just leave the times in Denver time. We will convert the times to and from the users’ timezones when we insert and retrieve the data from the database.

For the 2nd design decision,, it will be much easier to use the MySQL conversion functions than to use the PHP functions. This will allow us to simplify our handling of the timezone issue exclusively to our SQL queries and leave our PHP code largely alone.

Setting the Timezone in PHP

Inside our PHP script we can set the timezone for the current user by setting the TZ environment variable:

putenv('TZ='.getUserTimezone());

Once we have set the timezone, the PHP date function will properly convert timestamps to the user’s local timezone, and the strtotime function will propertly convert dates in the user’s local timezone back to timestamps.

Now we have 3 choices in how to store our dates in the database and interact with them.

Strategy 1: Store dates as timestamps

Since timestamps are not affected by timezones, we could just store all of our dates in INT columns as timestamps and convert them to dates using PHP. e.g.:

// inserting dates
$date = '2007-12-29 08:00:00';
mysql_query("insert into events ( ..., `startDate`, ...) values ( ..., '".strtotime($date)."', ...)");

// retrieving dates
$res = mysql_query("select `startDate` from events where ...");
list($date) = mysql_fetch_row($res);
$date = date('Y-m-d H:i:s', $date);  // convert the timestamp back to a date string

The main drawback to this strategy is style. Databases are equipped with DATE data types for a reason. By using INT datatypes to store our dates, we are missing out on some nice features for data manipulation etc… Plus it just doesn’t *feel* quite right to be using INT columns when we should be using DATETIME columns.

Strategy 2: Use FROM_UNIXTIME() and UNIX_TIMESTAMP() mysql functions

MySQL provides a some nice conversion functions that will allow us to work with timestamps yet still store our dates in proper DATE and DATETIME columns in the database. The FROM_UNIXTIME() function will convert a timestamp into a date string, and the UNIX_TIMESTAMP() function will convert a date string into a unix timestamp. So we can achieve the same as in Strategy 1 with the following:

// inserting dates
$date = '2007-12-29 08:00:00';
mysql_query("insert into events ( ..., `startDate`, ...) values ( ..., FROM_UNIXTIME('".strtotime($date)."'), ...)");

// retrieving dates
$res = mysql_query("select UNIX_TIMESTAMP(`startDate`) from events where ...");
list($date) = mysql_fetch_row($res);
$date = date('Y-m-d H:i:s', $date);  // convert the timestamp back to a date string

This strategy is a little bit better, in my opinion, because it uses the proper data types for the proper data. However it still requires that we use timestamps as an intermediary. In certain cases you may be unable to use timestamps (either because the rest of the system expects the SQL queries to return and accept date strings directly (not timestamps)), or because timestamps won’t handle the extreme dates in your application (e.g. timestamps are only valid for dates later than 1901 on most systems, and later that 1970 on some).

Strategy 3: Use the MySQL CONVERT_TZ() function

MySQL’s CONVERT_TZ() function will convert a date between two different timezones without requiring the intermediate step of converting to a timestamp. Theoretically, it should be able to accept input of the form:

CONVERT_TZ(`startDate`, 'Canada/Pacific', 'Canada/Eastern');

However if your server doesn’t have the timezone tables installed, then it won’t work correctly with these forms of timezones. The safest way seems to be to specify timezones in the form ‘+08:00’, meaning (8 hours behind GMT). e.g.

CONVERT_TZ(`startDate`, '+01:00', '-08:00');


This creates a small hurdle for us: How do we convert the user’s timezone (in the form ‘Canada/Pacific’) to an offset timezone like ‘+08:00’?

Here is a function that does just that:

/**
 * Returns the current timezone as an offset (e.g. +08:00) of GMT
 **/
utc_offset(){
        // Find the difference in seconds between GMT and local time.
        $diff = gmmktime(0,0,0,12,29,2007) - mktime(0,0,0,12,29,2007);
    $sign = ( ( $diff >= 0 ) ? '+' : '-');
    $diff = abs($diff);
    $hours = str_pad(strval(floor($diff/3600)), 2, '0',STR_PAD_LEFT);
    $minutes = str_pad(strval(floor($diff/60) % 60), 2, '0',STR_PAD_LEFT);
    
    return $sign.$hours.':'.$minutes;
    
}

So we can use our utc_offset() function to get the user’s timezone as an offset from GMT. We can pass this as one of the parameters for CONVERT_TZ(). The other timezone will be the system timezone, which we can conveniently specify as ‘SYSTEM’. e.g.:

-- Convert from -08:00 to system time
CONVERT_TZ(`startDate`, '-08:00', 'SYSTEM')

-- Convert from system time back to -08:00 time.
CONVERT_TZ(`startDate`, 'SYSTEM','-08:00')

We now have all the tools we need to adapt our examples from strategies 1 and 2:

// inserting dates
$date = '2007-12-29 08:00:00';
mysql_query("insert into events ( ..., `startDate`, ...) values ( ..., CONVERT_TZ('$date','".utc_offset()."','SYSTEM'), ...)");

// retrieving dates
$res = mysql_query("select CONVERT_TZ(`startDate`,'SYSTEM','".utc_offset()."') as `startDate` from events where ...");
list($date) = mysql_fetch_row($res);
echo $date;  // 2007-12-29 08:00:00

I used this strategy to add timezone support to Xataface because I needed a solution that would allow me to change the mysql queries without altering any of the code them uses them. It seems to work quite well.

Happy hunting!

Canada trademark Law

I found it interesting that Dataface Inc from Houston, TX decided to file a trademark for DATAFACE in Canada. I have been using the trade name Dataface since March 2005, in Canada. Their trademark application found
here
declares that they have used it in Canada since at least Nov. 5, 2007. That is over 2 years after the first use for my open source app.

According to the Canada trademark application instructions I can file a statement of opposition with the opposition office. However it costs $750 to file the claim and I haven’t been able to find a good example of what such a claim looks like. It doesn’t appear as though there is a standard form for this.

In doing some research on the validity of my claim to the trademark, I came across the Effigi Inc v Canada case in which it was declared that the date of first use was not relevant and that the dates of filing were the key decider as to who had rights to the trademark. This would indicate that in Canada it is essentially a first come first served system. Read more about this here.

Lessons Learned

  1. Don’t delay in filing for trademarks for any names you use, as it will be more costly and more of a hassle later on if another party decides to trademark your phrase.

This is obviously frustrating for someone like me who just wants to develop good open source software, and naturally needs to give a name to his products. It is unreasonable to trademark every name that I use since most of my products are released free to the public and for public benefit. It is a catch 22 because I cannot afford to trademark every phrase used in my applications, and I cannot afford to change the names of my applications when some tax-collecting company decides to trademark my phrase.

I hope that the powers that be might some day recognize the unjust aspects of this system and correct it so that artists, like myself, are protected from corporate interests.

I have created and released dozens of open source applications and have coined over 50 phrases concerned with labeling certain programming patterns. It would cost me about $10,000 to trademark all of these names and phrases (if I didn’t enlist the help of a lawyer). This seems rather ridiculous since most of these applications are free and open source, and I derive no money from them. Hence I would be paying a tax of $10,000 purely for the ability to name my things without having to worry about being sued or forced to change the name at a later date (which is a huge hassle, once you have amassed a lot of material on the topic.

I plan to blog on this issue more specifically once I have looked more closely into the protections available to artists with respect to corporate rent collectors.

Use of Dataface trademark

Well I figured this might happen eventually. A company in Texas has taken exception to my use of the name ‘Dataface’ for my open source application framework. It must rot their socks that I also come up #1,2 and 3 on Google for the search term “Dataface”.

I received a letter from their lawyers as follows:

The Buskop Law Group
Patents & Trademarks
4511 Dacoma Street
Houston, TX 77092
Telephone: 713-275-3400
Fax: 713-275-3419
www.buskoplaw.com

Dear Mr. Hannah,

We are the intellectual property cousel for Dataface, Inc., a company that awas founded in 1981. Dataface is the owner of the common law and U.S. federally protected trademark DATAFACE for a computer program in the field of facilitating conversion of legacy applications and data to a newer, more efficient technology using object-oriented programming techniques, as referenced by U.S. Trademark No. 2,617,053.

Dataface. Inc. has used the trademark DATAFACE in conjunction with their products and associated services since 1997. A copy of the referenced U.S. federal trademark registration is included herein as Attachment A. In addition, Dataface, Inc., has used the referenced trademark in Canada in conjunction with their products as well. Through extensive use of the mark, our client has developed a valuable goodwill in the DATAFACE trademark.

It has come to our client’s attention that you and your company are currently using a mark termed “Dataface” to promote and advertise products offered by your company on the company’s website at www.data-face.com. A copy of the webpage showing use of the term “Dataface” from the website at www.data-face.com is included herein as Attachment B.

Your use of the term Dataface is highly similar in look, sound, and connotation to our client’s common law and U.S. federally protected trademark DATAFACE. In addition, your use of the term “Dataface” is used in association with goods and/or services that are highly related to the goods and/or services offered by our client and covered under their U.S. federal trademark registration. Given the clear similarity between the term “Dataface”, as used on your company’s website, and our client’s trademark, your continued use of the term “Dataface” may cause a likelihood of confusion among consumers and customers of our client’s products and associated services.

Accordingly, my client requests that you discontinue the use of the www.data-face.com domain name and dicontinue the use, display, distribution of materials bearing the term “Dataface”, any derivations of the term “Dataface”, or any expression that comprises in whole or in part, our client’s trademark DATAFACE, within 30 days of service of this letter, and adopt another term for use in conjunction with your company’s products and services. Please contact our office if you have any additional questions regarding this matter.

Regards,
Buskop Law Group, P.C.

Wendy K. B. Buskop
Managing Patent Attorney

cc: Dataface, Inc.
Judith Osborne, VP Legal Affairs, Simon Fraser University

Cleverly, they applied for a canadian trademark a couple of weeks ago to solidify their case.