Script for importing timezone information from PHP into MySQL. Works by gathering information before removing the existing information from the MySQL tables. The gathering process takes ~17 sec. and the inserting takes only ~0.6 sec. This script decreases the downtime wwith about 2700% Modified from the example in php|architect’s Guide to Date a…

mysql_php_tzinfo_to_sql.php

#!/usr/bin/env php
<?php

/**
 * Script for importing timezone information from PHP into MySQL.
 * Modified from the example in php|architect’s Guide to Date and
 * Time Programming by Derick Rethans.
 * Works by gathering information before removing the existing information
 * from the MySQL tables.
 * The gathering process takes ~17 sec. and the inserting takes only ~0.6 sec.
 * This script decreases the downtime with about 2700%
 */

file_put_contents('php://stderr',
    "This script should be used like so: ./" . basename(__FILE__) . " |mysql\n");


$tz_list = timezone_identifiers_list();
//$tz_list = array_slice($tz_list, 0, 30);
$tz_list_c = count($tz_list);

$output = array();
$i = 1;
foreach ($tz_list as $tz_name) {
    file_put_contents('php://stderr', "Gatering information: " .
        floor(($i/$tz_list_c)*100) . "%\r");

    $output[] = "-- $tz_name";

    $output[] = "INSERT INTO `time_zone` (`Use_leap_seconds`) VALUES ('N');";
    $output[] = "SET @time_zone_id = LAST_INSERT_ID();";
    $output[] = "INSERT INTO `time_zone_name` (`Name`, `Time_zone_id`) " .
        "VALUES ('$tz_name', @time_zone_id);";


    $tzObj = new DateTimeZone($tz_name);
    $transitions = $tzObj->getTransitions();
    $types = array();
    $t = array();
    foreach ($transitions as $tr) {
        $key = "{$tr['offset']}|{$tr['isdst']}|{$tr['abbr']}";
        if (!in_array($key, $types)) {
            $types[] = $key;
        }
        if (-PHP_INT_MAX-1 == $tr['ts']) {
            continue;
        }
        $typeId = array_search($key, $types);
        $t[] = "(@time_zone_id, {$tr['ts']}, {$typeId})";
    }
    if (!empty($t)) {
        $output[] = "INSERT INTO `time_zone_transition` " .
            "(`Time_zone_id`, `Transition_time`, `Transition_type_id`) " .
            "VALUES " . implode(', ', $t) . ";";
    }


    $t = array();
    foreach ($types as $key => $type) {
        list($offset, $isdst, $abbr) = explode("|", $type);
        $isdst = $isdst ? '1' : '0';
        $t[] = "(@time_zone_id, $key, $offset, $isdst, '$abbr')";
    }
    if (!empty($t)) {
        $output[] = "INSERT INTO `time_zone_transition_type` " .
            "(`Time_zone_id`, `Transition_type_id`, `Offset`, `Is_DST`, `Abbreviation`) " .
            "VALUES " . implode(', ', $t) . ";";
    }

    $output[] = null;
    $i++;
}
file_put_contents('php://stderr', "\nWriting information to stdout.\n");

// Don't print anything out before we know everything is ok.
$time_start = microtime(true);

print <<<ENDE
USE `mysql`

TRUNCATE TABLE `time_zone`;
TRUNCATE TABLE `time_zone_name`;
TRUNCATE TABLE `time_zone_transition`;
TRUNCATE TABLE `time_zone_transition_type`;\n\n
ENDE;

print implode("\n", $output);

file_put_contents('php://stderr', "Done in " .
    round(microtime(true) - $time_start, 4) . " sec.\n");