<?php
$host = '127.0.0.1';
$port = '8889';
$user = 'root';
$pwd = 'root';
try{
$pdo = new PDO("mysql:host={$host};port={$port}",$user,$pwd);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
die($e->getMessage());
}
$sql[] = "create database if not exists datetest";
$sql[] = "use datetest";
$sql[] = 'create table if not exists test (openDate int(10))';
$sql[] = 'delete from test';
$sql[] = 'insert into test (openDate) values (-568080000)';
foreach($sql as $query):
try{
$pdo->exec($query);
} catch (PDOException $e) {
echo $e->getMessage();
die;
}
endforeach;
echo "++++++++++++++++\n";
echo 'PHP Tests' . "\n";
$timestamp = -568080000;
$pst = new datetimezone('PST');
$utc = new datetimezone('UTC');
//$base = new datetime($timestamp, $pst);
$year = 1952;
foreach(array($pst,$utc) as $timezone):
$inferredTimeStamp = new datetime($year . '-01-01', $timezone);
echo $inferredTimeStamp->getTimeStamp();
echo "\t" . $timezone->getName();
echo "\n";
endforeach;
echo "++++++++++++++++\n";
echo "Mysql tests \n";
function getMySqlOffset(){
global $pdo;
$sql = "select timediff(now(),convert_tz(now(),@@session.time_zone,'+00:00')) as output";
$s = $pdo->query($sql);
$row = $s->fetchObject();
list($h, $m, $s) = explode(":", $row->output);
if(substr($h,0,1) != '-') $h = '+'.$h;
$mysqlOffset = "$h:$m";
return $mysqlOffset;
}
$mysqlOffset = getMysqlOffset();
echo "current mysql timezone is $mysqlOffset\n";
echo "Force emulation of PC Homepage\n";
$pdo->exec("SET @@session.time_zone='-08:00'");
$mysqlOffset = getMysqlOffset();
echo "current mysql timezone is $mysqlOffset\n";
echo "Mysql date retrieval tests\n";
$sql = "select openDate as ts, from_unixtime(openDate) as df from test";
echo "Should show NULL on df \n";
try{
$s=$pdo->query($sql);
$row = $s->fetchObject();
print_r($row);
echo "\n";
} catch(PDOException $e){
echo $e->getMessage();
}
$sql = <<<SQL
SELECT @@session.time_zone as current_time_zone,
openDate as ts,
DATE_ADD(
FROM_UNIXTIME( 0 ) ,
INTERVAL OpenDate SECOND
) as df
FROM test
SQL;
echo "Should show a 1951 date for df \n";
try{
$s=$pdo->query($sql);
$row = $s->fetchObject();
print_r($row);
echo "\n";
} catch(PDOException $e){
echo $e->getMessage();
}
$sql = <<<SQL
SELECT @@session.time_zone as current_time_zone,
openDate as ts,
CONVERT_TZ(
DATE_ADD(
FROM_UNIXTIME( 0 ),
INTERVAL OpenDate SECOND
),
@@session.time_zone,"+0:00"
) as df
FROM test
SQL;
echo "Should show a 1952 date for df \n";
try{
$s=$pdo->query($sql);
$row = $s->fetchObject();
print_r($row);
echo "\n";
} catch(PDOException $e){
echo $e->getMessage();
}
echo "But we don't.... \nWhy ...\n";
echo "From the manual: \n";
echo "If the value falls out of the supported range of the TIMESTAMP type when converted from from_tz to UTC, no conversion occurs.\n";
echo "So mysql is implicitly converting to timestamps internally, and as we are dealing with early dates, the process is failing\n";
echo "So we need a different approach\n";
$sql = <<<SQL
SELECT @@session.time_zone as current_time_zone,
openDate as ts,
DATE_SUB(
DATE_ADD(
FROM_UNIXTIME( 0 ),
INTERVAL OpenDate SECOND
),
INTERVAL @@session.time_zone HOUR_MINUTE) as df
FROM test
SQL;
echo "Should show a 1952 date for df \n";
try{
$s=$pdo->query($sql);
$row = $s->fetchObject();
print_r($row);
echo "\n";
} catch(PDOException $e){
echo $e->getMessage();
}
echo "And we do .... \n\n";
echo "Now check whether this works with positive offsets too \n";
echo "Setting time zone to +5:00\n";
$pdo->exec("SET @@session.time_zone='+05:00'");
$mysqlOffset = getMysqlOffset();
echo "current mysql timezone is $mysqlOffset\n";
$sql = <<<SQL
SELECT @@session.time_zone as current_time_zone,
openDate as ts,
DATE_SUB(
DATE_ADD(
FROM_UNIXTIME( 0 ),
INTERVAL OpenDate SECOND
),
INTERVAL @@session.time_zone HOUR_MINUTE) as df
FROM test
SQL;
echo "Should show a midnight 1952 date for df \n";
try{
$s=$pdo->query($sql);
$row = $s->fetchObject();
print_r($row);
echo "\n";
} catch(PDOException $e){
echo $e->getMessage();
}
echo "And we do .... \n\n";
echo "So that works ...\n";
echo "Now let's try a normal approach, by setting the session time zone properly first before querying;";
echo "Setting time zone to +0:00\n";
$pdo->exec("SET @@session.time_zone='+00:00'");
$mysqlOffset = getMysqlOffset();
echo "current mysql timezone is $mysqlOffset\n";
$sql = <<<SQL
SELECT @@session.time_zone as current_time_zone,
openDate as ts,
DATE_ADD(
FROM_UNIXTIME( 0 ),
INTERVAL OpenDate SECOND
)
as df
FROM test
SQL;
echo "Should show a midnight 1952 date for df \n";
try{
$s=$pdo->query($sql);
$row = $s->fetchObject();
print_r($row);
echo "\n";
} catch(PDOException $e){
echo $e->getMessage();
}
echo "And we do.\n\n";
echo "FAOD - trying the full(ish) PC Homepage query with variable at 1951, 1952 and '' \n";
$sql = <<<SQL
SELECT @@session.time_zone,
year(DATE_ADD(
FROM_UNIXTIME( 0 ),
INTERVAL OpenDate SECOND
)) as df
FROM test
WHERE
IF( '%s' != '',
YEAR(
DATE_ADD(
FROM_UNIXTIME( 0 ) ,
INTERVAL OpenDate SECOND
)
),
''
) = '%s'
SQL;
try{
foreach(array('1951','1952','') as $var):
echo "Setting variable = ";
echo $var == '' ? "Empty string\n" : $var . "\n";
$query = sprintf($sql, $var, $var);
$s = $pdo->query($query);
$row = $s->fetchAll(PDO::FETCH_OBJ);
if(count($row) == 0):
echo "No results \n\n";
else:
print_r($row);
echo "\n";
endif;
endforeach;
} catch (PDOException $e) {
echo 'error';
echo $e->getMessage();
}
?>
