Timezone info for iCalendar

With the advent of 'Cloud' based services, we've had the opportunity to work on some pretty interesting projects. This project is no exception —” we've been developing a CalDAV server for users to share and edit their calendars on-the-go.

CalDav is a specification from the IETF, and is a protocol used to provide standardised read and write access to calendar data. We used the excellent SabreDAV package for PHP to help implement our calendar server, which drew its data from a FileMaker database — but thats another post for another day.

The beauty of CalDAV is that it's a standard which several different calendaring clients can connect to — not the least of which include iCal, and iOS calendars, along with Microsoft Outlook.

One of the major challenges of developing this system was not only sourcing timezone information, but converting it into a format that we could use directly in our CalDAV server.

Timezone Database

In comes the IANA Timezone Database (formerly the Olsen Database, and also known as tz, or zoneinfo). This is an excellent, and well maintained source of accurate timezone information, including daylight savings data. Best of all, its public-domain, and IANA provide the ability to rsync the entire database. This allows us to keep our timezone database fresh and up to date, without the hassle of manually updating it.

Useable Formats

The zoneinfo database itself is widely used, and because its a public-domain work, there are lots of programs written which will convert the database into a more useful format. Enter vzic. Lucky for us, this great little program that converts directly from the zoneinfo database, into iCalendar files for use in our CalDAV server!

So, we could access these files directly off the disk, then parse and insert them into our calendar data on-the-fly, or we could make the job that much easier and insert the whole lot into a MySQL database. We've written a PHP script which will create a SQL insert statement for the files generated by vzic — you can grab the file at the end of this post.

If you're automating the timezone updates, this script can be run direct from the command line using the following command:

$ php zoneinfo.php > zoneinfo.sql

This will dump the generated SQL into a file named zoneinfo.sql. From there, you can insert the zoneinfo data directly into your MySQL (or other compatible) database.

$ mysql --user=username --password=password db_name <p>Modify the constants at the top to set up the script for your database.</p><p></p><pre><?php 
 * A script which parses out timezone information generated from the zoneinfo database
 * and parsed into iCalendar format by vzic (using settings from mystreamtime compiled version).
 * @resource IANA Timezone Database - http://www.iana.org/time-zones
 * @resource vzic iCalendar converter - http://ostatic.com/vzic
 * @param string $argv[1] : Argument 1 is optional, and should be the path to the iCalendar files generated by vzic.
 *                             Defaults to zoneinfo folder in the current working directory.
 * @author Sam Sehnert - Copyright Digital Fusion 22 Feb 2012
 * @version 0.1.1
 */<p>// SQL database information (will generate MySQL insert statements).
define( 'SQL_TABLE',            'calendar_timezones' );
define( 'SQL_COLUMN_TZID',        'tzid' );
define( 'SQL_COLUMN_TZBASE',    'tzbase' );
define( 'SQL_COLUMN_TZSUB',        'tzsub' );
define( 'SQL_COLUMN_VTIMEZONE',    'vtimezone' );</p><p>// The regular expression to use for replacing unrequired timezone information.
define( 'TIMEZONE_ID_REGEX',    '/\/mystreamtime\.com\/[0-9]{8,}_[0-9]{1,}\//' );
define( 'ISCLI',                PHP_SAPI === 'cli' );</p><p>// Get the directory of the zoneinfo iCalendar collection.
if( ISCLI && isset( $argv[1] ) ){
    $dir = getcwd().'/'.preg_replace( '/^\.\//', '', $argv[1] );
} else {
    $dir = getcwd().'/zoneinfo';
}</p><p>// Make certain there is a trailing slash.
$dir = preg_replace( '/\/$/', '', $dir ).'/';</p><p>// Don't include any system files/folders.
$zones        = array();
$basezones    = scandir_omit_system( $dir ); </p><p>// If we found some basezones continue..
if( count( $basezones ) > 0 ){

    // Loop over each of the folders (base zones).
    foreach( $basezones as $zone ){

        // Get the subzones in the timezone list.
        $subzones = scandir_omit_system( $dir.$zone );

        // If we found some subzones continue..
        if( count( $subzones ) > 0 ){

            // Loop over each of the timezone base zones and get the iCalendar timezone data.
            foreach( $subzones as $path ){

                // strip the .ics from the end.
                $area = substr( $path, 0, -4 );

                // Get the timezone block ONLY.
                $tz = array_slice(explode("\n",file_get_contents( $dir.$zone.'/'.$path )),3);
                $tz = implode("\n",array_slice($tz,0,count($tz)-2));

                // Store the vcal data in an array.
                $zones[$zone.'/'.$area] = preg_replace( TIMEZONE_ID_REGEX, '', $tz );
        } else {
} else {
}</p><p>$tz_insert = array();</p><p>// Loop over the timezones and vtimezones.
foreach( $zones as $tzid => $vtimezone ){

    $tz = explode( '/', $tzid );

    // Add this insert block into the insert array.
    $tz_insert[] = '( \''.escape_vtimezone( $tzid ).'\', \''.escape_vtimezone( $tz[0] ).'\', \''.escape_vtimezone( $tz[1] ).'\', \''.escape_vtimezone( $vtimezone )."' )";
}</p><p>// Output <pre> tags for presentation when executing via a web browser.
if( !ISCLI ) echo '<pre>';</p><p>// Build and output the insert statement.
echo 'INSERT INTO '.strip_backtick_escape( SQL_TABLE ).' ( '.
            strip_backtick_escape( SQL_COLUMN_TZID ).', '.
            strip_backtick_escape( SQL_COLUMN_TZBASE ).', '.
            strip_backtick_escape( SQL_COLUMN_TZSUB ).', '.
            strip_backtick_escape( SQL_COLUMN_VTIMEZONE ).
        " ) VALUES \n";</p><p>// Print the actual timezone data.
echo implode( $tz_insert, ",\n" );</p><p>if( !ISCLI ) echo '

* Strips backticks and escape characters from the input.
* @param string $value : The value sanitize.
* @return string : The sanitized value.
function strip_backtick_escape( $value ){
return '`'.str_replace( '`\\', '', $value ).'`';

* Escapes the vtimezone block for use in the database.
* @param string $value : The value sanitize.
* @return string : The sanitized value.
function escape_vtimezone( $value ){
return preg_replace( "/\n/", '\n', addslashes( $value ) );

* Scans the given directory, returning non system folders.
* @param string $dir : The directory to list files and folders from.
* @return array : An array of files / folders in the given directory.
function scandir_omit_system( $dir ){
return array_filter( scandir( $dir ), function ( $val ){
return !preg_match( '/^\./', $val );

// Exit... all is OK.

Write a response...