i see. have you checked the mysql version to ensure that it is supporting the full gamut of possible unix timestamps - you should be ok but it is worth checking?
the timestamp itself is timezone agnostic, of course, but when you convert to/from a date the timezone offset is taken into account. and depending on
how you are inserting the timestamp into the database (as an integer where the conversion is done in php, or as a date where the conversion is done in mysql) you will/may get different results as the timezones that are in play may be different.
if a default time zone is not set then php will fire a warning each time a timezone dependent function is called. so if you are not seeing warnings in your error log then you should be ok. but you still need to know
what timezone is in use in your scripts and in your mysql installation to know whether you need to do any corrections or overrides.
you set a default time zone in your php script like this
Code:
date_default_timezone_set('UTC'); //for utc ...
you can also set the timezone that mysql will use like this
Code:
//connect to mysql
mysql_query("set time_zone = 'CET' "); //to set to central european time
note for the above to work with mysql you MUST have populated the mysql timezone tables. this is not done automatically so because you are on a limited host you may find this difficult. you can always set the timezone using the UTC offset method
Code:
SET time_zone = '-05:00';
but you will need to compute for daylight savings etc.
to get the mysql time zone in php you can use this function
Code:
<?php
$host = '';
$user = '';
$pass = '';
try{
$pdo = new PDO("mysql:host={$host}", $user, $pass);
$pdo->exec('set names utf8');
$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_OBJ);
}catch (PDOException $e){
print "Error!: " . $e->getMessage() . "<br/>";
die();
}
function getMysqlTimeZone(){
global $pdo;
foreach ($pdo->query('Select @@session.time_zone as tz') as $row):
$tz = $row->tz;
break;// just in case
endforeach;
if($tz == 'SYSTEM'):
foreach($pdo->query('Select @@system.time_zone as tz') as $row):
$tz = $row->tz;
break;
endforeach;
endif;
return $tz;
}
?>
So if you wanted a programmatic method to standardise your server and mysql timezones for peace of mind you could do this as part of your connection routine.
Code:
function normaliseTimeZones($myTimesZone = 'US/Eastern'){
/*set php */
date_default_timezone_set($myTimeZone);
/* get today's offset */
$date = new datetime('now');
$tz = new datetimezone($myTimeZone);
$offset = $tz->getOffset();
$sign = $offset >= 0 ? '+' : '-';
$offset = $sign . gmdate('H:i', abs($offset));
/* write to mysql */
$pdo->exec("Set time_zone = '$offset'");
}
if your timezones get too messy then you might be well advised to use the date/time class in php instead of strtotime and date. they are (essentially) low level wrappers but take some of the heavy lifting away for you. e.g
Code:
date_default_timezone_set('UTC'); //you must have a default time zone set. this can be any valid time zone
$date = new datetime;
$EST = new DateTimeZone( 'US/Eastern' );
$PST = new DateTimeZone(' US/Pacific' );
$date->setTimeStamp( $timestampFromMysql );
$date->setTimeZone( $EST );
echo $date->format( 'Y-m-d H:i A' ) . "<br/>\n";
$date->setTimeZone( $PST );
echo $date->format( 'Y-m-d H:i A' ) . "<br/>\n";
expected output would be
Code:
1969-09-03 16:52 PM
1969-09-03 13:52 PM
