Home » Help » Documentation » 2.8 Admin Guide » Install and upgrade » Upgrading openx

Upgrading time zones

If you are upgrading from OpenX 2.6 this section can be ignored. If you are installing OpenX for the first time or upgrading from OpenX 2.4 or earlier, however, please read the following.

The problem with time zones

In OpenX 2.4 and earlier, the logging of delivery data (requests, impressions, clicks and conversions) was performed using the time zone of the server that OpenX had been installed on.
This approach had a number of issues, including:

  • The OpenX administrator might not have had the required privileges to change the time zone of the server OpenX is installed on. (For example, OpenX might have been installed on a server provided by an ISP, where the OpenX administrator did not have 'root' access.) This might have resulted in OpenX displaying statistics in a time zone different to that desired by the user.
  • If the server time zone was in a location where the time changed twice a year (for example, between standard time and daylight saving time) then twice a year, when clocks go forward or backward, there would be a gap or duplication of one hour's statistics.
  • If the OpenX installation needed to be moved to a new server where the time zone of the server was different, or if the server administrator wanted to change the time zone, not only would there be a similar problem to the above (a gap or duplication in logging), but also all past statistics would not be migrated to the new time zone.

Addressing the issue in OpenX 2.6

To address this issue, since OpenX 2.6 the logging of delivery data has been performed using Coordinated Universal Time (UTC). This means that:

  • The time zone of the server does not matter; as long as the time zone and current time are set correctly on the server, all requests, impressions, clicks and conversions will be correctly logged in UTC.
  • On servers where the time zone results in the clock changing between standard time and daylight saving time, the logging of data will not be affected.
  • The time zone of a server can be changed without affecting the logging of data.
  • An OpenX installation can be migrated between servers in different time zones without affecting the data.
  • It is possible for different Manager Accounts in OpenX to set different time zones as a preference, and view their statistics in a time zone that is relevant to them, rather than every Manager Account having to use just the one time zone.

Migration of Past Statistics

The fact that the logging of delivery data has changed from the server time zone to UTC means that if OpenX is upgraded from OpenX 2.4 (or earlier) to OpenX 2.6 (or later), then the past statistics will no longer be in the correct time zone. (Unless, of course, the server time zone was already UTC.)

The upgrade wizard does not convert your past statistics to UTC. You will need to read the following sections and decide which course of action suits you best.

Do Nothing

For most users of OpenX, it is probably not that important to worry about the migration of past statistics to UTC. The fact that the logging of data was not always correct in the past (for example, when time zones changed between standard time and daylight saving time) means that you will gain little by carrying out the complex process of trying to migrate all your past data to UTC.

The important thing to remember is that your total number of requests, impressions, clicks and conversions will not be affected if you do nothing. You can still rely on these values if you need to look at past data.All that it will mean is that if you view your past data at the daily or hourly level, the exact day/hour that statistics are reported may change a little (depending on the difference between your server's time zone and UTC).

When you are looking at past statistics OpenX will warn you when the exact day/hour may be slightly off, so there's no need to worry about remembering to account for this.

If this is acceptable you can simply carry on with your OpenX upgrade. Do not select the option that indicates you have upgraded your past data. Otherwise, when you are looking at past statistics that might be affected by the upgrade to logging in on UTC you might not be shown the warning on statistics screens and in reports.

Upgrade your past statistics before upgrading

If it is absolutely vital for you and your business that past statistics are not displayed in a slightly incorrect day/hour format in any way, then it is possible for you to manually upgrade your past statistics to ensure that it is in UTC. However, this process may take some time (potentially days!) depending on the size of your database. This is why the OpenX upgrade wizard does not attempt to do this for you. You will need to have access to run commands directly in your database to perform these changes.

Preparing for the UTC Update

In order to upgrade your past statistics to UTC you must stop the logging of new data in OpenX. This allows all past data to be correctly converted without new data being logged in the local server time zone.

To do this:

Disable Request, Impression, Click and Tracker Data Logging

  • Disable all creatives linked to your Zones so that no creative impressions or clicks are logged
  • If you are using OpenX 2.4 or Max Media Manager v0.3, then in your installation disable the logging of requests and tracker impressions.
    • Alternatively, if you only have OpenX tags in a limited number of places you could also simply remove all the tags from your sites.

Ensure All Existing Data is Summarised

    If you are using OpenX 2.4 or Max Media Manager v0.3, wait for the maintenance script to run and summarise all of your data.

Disable Maintenance

  • If you are using OpenX 2.4 or Max Media Manager v0.3, disable your maintenance cronjob if you have one set up. During the upgrade process no new data is being logged, so there is no need to run the maintenance script.

Backup Your OpenX Database

  • In case you need to, please make sure your backup is known to be good and that you know how to restore it.Determine the Difference Between the Server Timezone and UTC
  • You can use free tools like The World Clock to check the exact difference between your local time zone and UTC.
  • You can determine your server's time zone by looking at its date/time information. However, it can help to see which time zone PHP thinks your server is in. To do so, put the following code in a PHP file on the web server that OpenX is installed on and view this page via a web browser:
    $calculated = false;
    if (version_compare(phpversion(), '5.1.0', '>=')) {
    // Great! The PHP version is >= 5.1.0, so simply
    // use the built in date_default_timezone_get()
    // function, and know it's all good
    $tz = date_default_timezone_get();
    } else {
    // Boo, we have to rely on the dodgy old TZ
    // environment variable stuff
    $tz = getenv('TZ');
    if ($tz === false || $tz === '') {
    // Even worse! The user doesn't have a TZ
    // variable, so we have to try and calcuate
    // the timezone for the user
    $calculated = true;
    unset($tz);
    $diff = date('O');
    $diffSign = substr($diff, 0, 1);
    if ($diffSign == "+") {
    $diffHour = (int) substr($diff, 1, 2) - date('I'); // minus 1 hour if date in DST
    } else {
    $diffHour = (int) substr($diff, 1, 2) + date('I'); // add 1 hour if date in DST
    }
    $diffMin = (int) substr($diff, 3, 2);
    $offset = (($diffHour * 60) + ($diffMin)) * 60 * 1000; // Milliseconds
    $offset = $diffSign . $offset;
    global $_DATE_TIMEZONE_DATA;
    if (!isset($_DATE_TIMEZONE_DATA)) {
    include('Date/TimeZone.php');
    }
    reset($_DATE_TIMEZONE_DATA);
    foreach (array_keys($_DATE_TIMEZONE_DATA) as $key) {
    if ($_DATE_TIMEZONE_DATA[$key]['offset'] == $offset) {
    $tz = $key;
    break;
    }
    }
    }
    }
    echo "Your server timezone is: $tz
    \n";
    if ($calculated) {
    echo "Note that this timezone is calculated. You should set up your timzone correctly.";
    }
    ?>

UTC upgrade process

Upgrade for OpenX 2.4

The following examples assumes that your server is in New York, or 5 hours behind UTC. Obviously you will need to adapt the examples below for your server's time zone.

OpenX 2.4 conversion tracking steps

If you are performing conversion tracking with your Openx 2.4 installation, you will need to upgrade your existing raw data:

  • If you perform conversion tracking against impressions, update the ox_data_raw_ad_impression table:
    UPDATE ox_data_raw_ad_impression SET date_time = date_time + INTERVAL 5 HOUR;
  • If you perform conversion tracking against clicks, update the ox_data_raw_ad_click table:
    UPDATE ox_data_raw_ad_click SET date_time = date_time + INTERVAL 5 HOUR;
  • Update the ox_data_intermediate_ad_connection table:
    UPDATE ox_data_intermediate_ad_connection SET
    tracker_date_time = tracker_date_time + INTERVAL 5 HOUR,
    connection_date_time = connection_date_time + INTERVAL 5 HOUR;
  • Update the ox_data_intermediate_ad_variable_value table:
    UPDATE ox_data_raw_tracker_variable_value SET date_time = date_time + INTERVAL 5 HOUR;

OpenX 2.4 Common Steps

Regardless of whether you use your OpenX 2.4 installation for conversion tracking or not, you will need to:

  • Update the ox_data_intermediate_ad table:
    UPDATE ox_data_intermediate_ad SET
    day = DATE(TIMESTAMP(day) + INTERVAL hour HOUR + INTERVAL 5 HOUR),
    hour = HOUR(TIMESTAMP(day) + INTERVAL hour HOUR + INTERVAL 5 HOUR),
    operation_interval_id = MOD(operation_interval_id + 5, 167),
    interval_start = interval_start + INTERVAL 5 HOUR,
    interval_end = interval_end + INTERVAL 5 HOUR;
  • Update the ox_data_summary_ad_hourly table:
    UPDATE ox_data_summary_ad_hourly SET
    day = DATE(TIMESTAMP(day) + INTERVAL hour HOUR + INTERVAL 5 HOUR),
    hour = HOUR(TIMESTAMP(day) + INTERVAL hour HOUR + INTERVAL 5 HOUR);
  • Update the ox_data_summary_ad_zone_assoc table:
    UPDATE ox_data_summary_ad_zone_assoc SET
    operation_interval_id = MOD(operation_interval_id + 5, 167),
    interval_start = interval_start + INTERVAL 5 HOUR,
    interval_end = interval_end + INTERVAL 5 HOUR,
    created = created + INTERVAL 5 HOUR,
    expired = expired + INTERVAL 5 HOUR;
  • Update the ox_acls table.

Any targeting criteria (delivery limitations) that you have set up that target creatives to a certain hour need to be converted to UTC. This is easiest to do with a simple PHP script:

$host = 'localhost';
$port = 3306;
$username = 'user';
$password = 'password';
$database = 'openx_db';

$prefix = 'ox_';
$table = 'acls';

$hours = 5;

$rConnection = mysql_connect("$host:$port", $username, $password);
if (!$rConnection) {
die('Could not connect: ' . mysql_error());
}

$result = mysql_select_db($database, $rConnection);
if (!$result) {
die('Could not use database: ' . mysql_error());
}
$query = "SELECT * FROM $prefix$table WHERE type = 'Time:Hour'";
$rResult = mysql_query($query, $rConnection);
if (!$rResult) {
die('Could not select targeting criteria for update: ' . mysql_error());
}
while ($aRow = mysql_fetch_assoc($rResult)) {
echo "Updating time-based targeting criteria for creative ID: {$aRow['bannerid']}\n";
// Prepare the start of the update message
$message = " Changed time-based targeting crieteria from:\n";
$message .= " '{$aRow['data']}'\n";
$message .= " to\n";
// Prepare the new time-based data
$aTimeData = explode(',', $aRow['data']);
foreach ($aTimeData as $key => $value) {
$aTimeData[$key] = ($value + 5) % 24;
}
$newData = implode(',', $aTimeData);
// Update the database
$updateQuery = "UPDATE $prefix$table SET
data = '$newData' WHERE bannerid = '{$aRow['bannerid']}'
AND logical = '{$aRow['logical']}'
AND type = '{$aRow['type']}'
AND comparison = '{$aRow['comparison']}'
AND data = '{$aRow['data']}'
AND executionorder = '{$aRow['executionorder']}'
";
$result = mysql_query($updateQuery);
if (!$result) {
die('Failed to execute query: ' . mysql_error());
}
// Complete and display the results of the update
$message .= " '$newData'\n";
}
echo $message;

mysql_close($rConnection);

?>

  • Update the ox_log_maintenance_statistics table:
    UPDATE ox_log_maintenance_statistics SET
    start_run = start_run + INTERVAL 5 HOUR,
    end_run = end_run + INTERVAL 5 HOUR,
    updated_to = updated_to + INTERVAL 5 HOUR;
  • Update the ox_log_maintenance_priority table:
    UPDATE ox_log_maintenance_priority SET
    start_run = start_run + INTERVAL 5 HOUR,
    end_run = end_run + INTERVAL 5 HOUR,
    updated_to = updated_to + INTERVAL 5 HOUR;
  • Update the ox_userlog table:
    UPDATE ox_userlog SET timestamp = timestamp + 5 * 3600;
  • Update the ox_upgrade_action table:
    UPDATE ox_upgrade_action SET updated = updated + INTERVAL 5 HOUR;

Upgrade for Openads 2.0.11-pr1

The following examples assumes that your server is in New York, or 5 hours behind UTC. You will, of course, need to adapt the examples below for your server's time zone.
Openads 2.0.11-pr1 compact statistics mode steps

If you are running Openads 2.0.11-pr1 with "Compact" statistics:

  • Update the phpads_adstats table:
    UPDATE phpads_adstats SET
    day = DATE(TIMESTAMP(day) + INTERVAL hour HOUR + INTERVAL 5 HOUR),
    hour = HOUR(TIMESTAMP(day) + INTERVAL hour HOUR + INTERVAL 5 HOUR);

Once you have completed this, proceed to the Openads 2.0.11-pr1 Common Steps section below.

Openads 2.0.11-pr1 verbose statistics mode steps

If you are running Openads 2.0.11-pr1 with "Verbose" statistics:

  • Update the phpads_adviews table:
    UPDATE phpads_adviews SET t_stamp = t_stamp + INTERVAL 5 HOUR;
  • Update the phpads_clicks table:
    UPDATE phpads_adclicks SET t_stamp = t_stamp + INTERVAL 5 HOUR;

Once you have completed this, proceed to the Openads 2.0.11-pr1 Common Steps section below.

Openads 2.0.11-pr1 common steps

Once you have updated your statistics in either the Openads 2.0.11-pr1 Compact Statistics Format or Openads 2.0.11-pr1 Verbose Statistics Format sections above:

  • Update the phpads_acls table

Any targeting criteria (delivery limitations) that you have set up which target creatives to a certain hour need to be converted to UTC. This is easiest to do with a simple PHP script:

$host = 'localhost';
$port = 3306;
$username = 'user';
$password = 'password';
$database = 'openads_db';

$prefix = 'phpads_';
$table = 'acls';

$hours = 5;

$rConnection = mysql_connect("$host:$port", $username, $password);
if (!$rConnection) {
die('Could not connect: ' . mysql_error());
}

$result = mysql_select_db($database, $rConnection);
if (!$result) {
die('Could not use database: ' . mysql_error());
}

$query = "SELECT * FROM $prefix$table WHERE type = 'time'";
$rResult = mysql_query($query, $rConnection);
if (!$rResult) {
die('Could not select targeting criteria for update: ' . mysql_error());
}

while ($aRow = mysql_fetch_assoc($rResult)) {
echo "Updating time-based targeting criteria for creative ID: {$aRow['bannerid']}\n";
// Prepare the start of the update message
$message = " Changed time-based targeting crieteria from:\n";
$message .= " '{$aRow['data']}'\n";
$message .= " to\n";

// Prepare the new time-based data
$aTimeData = explode(',', $aRow['data']);
foreach ($aTimeData as $key => $value) {
$aTimeData[$key] = ($value + 5) % 24;
}
$newData = implode(',', $aTimeData);
// Update the database
$updateQuery = "UPDATE $prefix$table SET data = '$newData' WHERE
bannerid = '{$aRow['bannerid']}'
AND logical = '{$aRow['logical']}'
AND type = '{$aRow['type']}'
AND comparison = '{$aRow['comparison']}'
AND data = '{$aRow['data']}'
AND executionorder = '{$aRow['executionorder']}'";
$result = mysql_query($updateQuery);
if (!$result) {
die('Failed to execute query: ' . mysql_error());
}
// Complete and display the results of the update
$message .= " '$newData'\n";
}
echo $message;

mysql_close($rConnection);
?>

  • Update the phpads_userlog table:
    UPDATE phpads_userlog SET timestamp = timestamp + 5 * 3600;

    • Upgrade for Openads 2.0.11-pr1 for PostgreSQL

    Community suggestions welcome!

    Upgrade for Max Media Manager v0.3 (Openads 2.3)

    If you are running Max Media Manager v0.3 (aka Openads 2.3), please follow the INTR:upgrade steps for OpenX 2.4. Note, however, that you might need to adjust the database prefix. Also note that the 'ox_upgrade_action' table does not exist in MMM v0.3, so you will not need to upgrade this table.

    Upgrade for Max Media Manager v0.1

    The following example assumes that your server is in New York, or 5 hours behind UTC. Obviously, you will need to adapt the examples below for your server's time zone.
    Max Media Manager v0.1 Conversion Tracking Steps

    If you are performing conversion tracking with your Max Media Manager v0.1 installation you will need to upgrade your existing raw data:

    • If you perform conversion tracking against impressions, update the max_adviews table:
      UPDATE max_adviews SET t_stamp = t_stamp + INTERVAL 5 HOUR;
    • If you perform conversion tracking against clicks, update the max_clicks table:
      UPDATE max_adclicks SET t_stamp = t_stamp + INTERVAL 5 HOUR;
    • Update the max_adconversions table:
      UPDATE max_adconversions SET t_stamp = t_stamp + INTERVAL 5 HOUR;
    • Update the max_conversionlog table:
      UPDATE max_conversionlog SET
      t_stamp = t_stamp + INTERVAL 5 HOUR,
      action_t_stamp = action_t_stamp + INTERVAL 5 HOUR;
    • Update the max_variablevalues table:
      UPDATE max_variablevalues SET t_stamp = t_stamp + INTERVAL 5 HOUR;

    Max Media Manaver v0.1 common steps

    Regardless of whether you use your Max Media Manaver v0.1 installation for conversion tracking or not you will need to:

    • Update the max_adstats table:
      UPDATE max_adstats SET
      day = DATE(TIMESTAMP(day) + INTERVAL hour HOUR + INTERVAL 5 HOUR),
      hour = HOUR(TIMESTAMP(day) + INTERVAL hour HOUR + INTERVAL 5 HOUR);
    • Update the max_log_maintenance table:
      UPDATE max_log_maintenance SET
      start_run = start_run + INTERVAL 5 HOUR,
      end_run = end_run + INTERVAL 5 HOUR;
      • Update the max_acls table.

    Any targeting criteria (delivery limitations) you have set up that target creatives to a certain hour need to be converted to UTC. This is easiest to do with a simple PHP script:

    $host = 'localhost';
    $port = 3306;
    $username = 'user';
    $password = 'password';
    $database = 'max_db';

    $prefix = 'max_';
    $table = 'acls';

    $hours = 5;

    $rConnection = mysql_connect("$host:$port", $username, $password);
    if (!$rConnection) {
    die('Could not connect: ' . mysql_error());
    }

    $result = mysql_select_db($database, $rConnection);
    if (!$result) {
    die('Could not use database: ' . mysql_error());
    }

    $query = "SELECT * FROM $prefix$table WHERE type = 'time'";
    $rResult = mysql_query($query, $rConnection);
    if (!$rResult) {
    die('Could not select targeting criteria for update: ' . mysql_error());
    }

    while ($aRow = mysql_fetch_assoc($rResult)) {
    echo "Updating time-based targeting criteria for creative ID: {$aRow['bannerid']}\n";
    // Prepare the start of the update message
    $message = " Changed time-based targeting crieteria from:\n";
    $message .= " '{$aRow['data']}'\n";
    $message .= " to\n";
    // Prepare the new time-based data
    $aTimeData = explode(',', $aRow['data']);
    foreach ($aTimeData as $key => $value) {
    $aTimeData[$key] = ($value + 5) % 24;
    }
    $newData = implode(',', $aTimeData);

    // Update the database
    $updateQuery = "UPDATE $prefix$table SET data = '$newData' WHERE
    bannerid = '{$aRow['bannerid']}'
    AND logical = '{$aRow['logical']}'
    AND type = '{$aRow['type']}'
    AND comparison = '{$aRow['comparison']}'
    AND data = '{$aRow['data']}'
    AND executionorder = '{$aRow['executionorder']}'";

    $result = mysql_query($updateQuery);
    if (!$result) {
    die('Failed to execute query: ' . mysql_error());
    }
    // Complete and display the results of the update
    $message .= " '$newData'\n";
    }
    echo $message;

    mysql_close($rConnection);

    ?>

  • Update the max_userlog table:
    UPDATE max_userlog SET timestamp = timestamp + 5 * 3600;

    • Post-UTC upgrade

    Upgrade to OpenX 2.6

    Once all your existing data has been converted to UTC by following one of the above processes you will be ready to upgrade to OpenX 2.6 (or later) via the upgrade wizard. During the upgrade, select the option that indicates you have upgraded your past data.

    Re-compile delivery targeting

    Once you have completed the upgrade to OpenX 2.6 (or later) via the upgrade wizard, log in with your Adminstrator account, click the Configuration tab on the homepage, and set the Banner Delivery Settings to re-compile the delivery targeting (delivery limitations). This will ensure that all of your delivery targeting rules are updated to use the new UTC based rules you set with the above SQL commands.

    Restore or set up maintenance

    If you were running OpenX 2.4 or Max Media Manager v0.3, don't forget to re-enable your maintenance script. If you have upgraded from Openads 2.0 it is recommended that you set up a maintenance script. If you have upgraded from Max Media Manager v0.1, the recommended way of setting up a maintenance script has changed.

    Re-enable creatives

    If you disabled your creatives and/or disabled request and tracker impression logging, you can now re-enable the creatives and logging of data as required. Alternatively, if you simply removed your tags from your website(s) you can now restore them, However, for improved performance you might want to consider generating new tags with your latest version of OpenX.

    <div class="home_box"> <p id="home_box_list_header">OpenX 2.8 Admin Guide</p> <ul> <li><a href="/docs/2.8/adminguide/Install+and+upgrade">Install and Upgrade</a> <ul> <li><a href="/docs/2.8/adminguide/System+requirements">System requirements</a></li> <li><a href="/docs/2.8/adminguide/Types+of+installation">Types of installation</a></li> <li><a href="/docs/2.8/adminguide/Securing+OpenX">Securing OpenX</a></li> <li><a href="/docs/2.8/adminguide/Upgrading+OpenX">Upgrading OpenX</a> <ul> <li><a href="/docs/2.8/adminguide/Preparing+a+test+environment">Preparing a test environment</a></li> <li><a href="/docs/2.8/adminguide/Preparing+for+an+upgrade">Preparing for an upgrade</a></li> <li><a href="/docs/2.8/adminguide/Running+the+upgrade+wizard">Running the upgrade wizard</a></li> <li><a href="/docs/2.8/adminguide/Upgrade+Time+Zones">Upgrading time zones</a></li> <li><a href="/docs/2.8/adminguide/Upgrading+Character+Encoding">Upgrading character encoding</a></li> </ul> </li> <li><a href="/docs/2.8/adminguide/Installing+OpenX">Installing OpenX</a></li> <li><a href="/docs/2.8/adminguide/Running+maintenance">Running maintenance</a></li> </ul> </li> <li><a href="/docs/2.8/adminguide/Configuring+OpenX">Configuring OpenX</a></li> <li><a href="/docs/2.8/adminguide/Managing+security">Managing Security</a></li> <li><a href="/docs/2.8/adminguide/Managing+performance">Managing Performance</a></li> <li><a href="/docs/2.8/adminguide/Distributed+statistics">Distributed Statistics</a></li> <li><a href="/docs/2.8/adminguide/channel+management">Channel Management</a></li> <li><a href="/docs/2.8/adminguide/manager+accounts ">Manager Accounts</a></li> <li><a href="/docs/2.8/adminguide/User+management ">User Management</a></li> <li><a href="/docs/2.8/adminguide/Plugins+ ">Plugins</a></li> </ul> </div>