Retrieving and Formating MySQL Timestamp Field To Local Time

[energylevel]

How to output a MySQL timestamp field to correct local time with daylight saving with PHP?

 

Local time is set in php.ini with: date.timezone = "Europe/London"

 

Confirmed set with:   <?php echo date_default_timezone_get(); ?>

 

But when I echo the timestamp field it does not display correct UK time for the record (an hour behind correct UK time).

 

<?php echo date('jS F Y - g:ia', strtotime($row['Date'])); ?>

 

[Psycho]

I'm no expert on this. But, I think the problem is MySQL - not PHP. MySQL is returning a string to PHP (not a timestamp). So, whatever you do with the value after you get it back from MySQL really doesn't make any difference. Either you need to tell MySQL in which timezone the value should be returned or you need a way to tell PHP what timezone the source is from in order to modify it for the expected output. Again, not sure the correct method,

 

EDIT: Another option: I know that you can have MySQL return the timestamp as a UNIX timestamp instead of a string value. Then it should be interpreted by PHP as you want.

 

EDIT #2: Look at this: http://www.sitepoint.com/synchronize-php-mysql-timezone-configuration/ Based on the information in that blog, you do need to set the timezone offset for MySQL before executing your queries.

Edited May 28, 2014 by Psycho

[energylevel]

This seemed to sort as a quick solution:

<?php 
date_default_timezone_set('Europe/London');
mysql_query("SET `time_zone` = '".date('P')."'");
?>

[QuickOldCar]

You can detect if daylight savings something like this

 

 

<?php
function daylightSavings()
{
    $date = new DateTime(date('Y-m-d H:i:s') . 'Europe/London');
    if ($date->format('I') == "1") {
        return TRUE;
    } else {
        return FALSE;
    }
}
 
//show current time
date_default_timezone_set('Europe/London');
$my_date = date('Y-m-d H:i:s');
echo $my_date;
 
echo "<br />";
 
//show modified time
if (daylightSavings()) {
    $my_date = date('Y-m-d H:i:s', strtotime('+1 hour'));
    echo $my_date;
}
?>