Daylight saving correction in PHP

[mastubbs]

Hi all,

 

Ok so i have a bit of a tricky question. I am using a query to return just time from a datetime variable '_sfm_form_submision_time_' based on a few conditions as follows:

if(isset($_GET['mrn']))
{
$Search = $_GET['mrn'];
$Find_Query1 = mysql_query("SELECT DATE_FORMAT(_sfm_form_submision_time_,'%H:%i') AS time, SBP FROM obs WHERE mrn='$Search' AND DATE(_sfm_form_submision_time_) = $chosendate() order by time ASC");
if(!$Find_Query1)
{
die(mysql_error());
}
while($row = mysql_fetch_assoc($Find_Query1))
{
    echo '<br/> TIME: '.$row['time'];
    echo '<br/> SBP: '.$row['SBP'];
	echo '<br/>';
	
}
$numCount = mysql_num_rows($Find_Query1);

if ($numCount < 1) 
{
print("no sbp/time found for that mrn on chosen date");
}

However, i want to make a correction to the returned 'time', for daylight saving. In the UK daylight saving time (British Summer Time - BST) starts at 1am on the last Sunday in March (1am GMT), and finishes at 2am BST (ie 1am GMT) on the last Sunday in October. To make things more complex, my server saves datetimes as 4 hours behind GMT (which i cant change), so i also want to correct for this. So, if the date '_sfm_form_submision_time_' was recorded was in daylight saving time I want to add 3 hours to 'time', otherwise i want to add 4 hours to 'time'. Does anyone have any clue how to do this?? Any help anyone can give would be amazing! 

 

Thanks

 

Matt

[requinix]

Before you try to do the arithmetic yourself, see if there are other solutions.

 

What happens if you do a

mysql_query("SET time_zone = 'Europe/London'");

beforehand?

[mastubbs]

Before you try to do the arithmetic yourself, see if there are other solutions.

 

What happens if you do a

mysql_query("SET time_zone = 'Europe/London'");

beforehand?

 

 

Hia,

 

You mean like this? 

$Search = $_GET['mrn'];
mysql_query("SET time_zone = 'Europe/London'");
$Find_Query1 = mysql_query("SELECT DATE_FORMAT(_sfm_form_submision_time_,'%H:%i') AS time, SBP FROM obs WHERE mrn='$Search' AND DATE(_sfm_form_submision_time_) = CURDATE() order by time ASC");
if(!$Find_Query1)
{
die(mysql_error());
}

It doesn't change the times returned, they are still 4 hours behind :-(

[mac_gyver]

next try this - http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_convert-tz

 

----------

 

my server saves datetimes as 4 hours behind GMT (which i cant change)

 

well, yes you can. if you are using php date/time functions you can set the timezone php uses to GMT using date_default_timezone_set() and you can do the same for mysql date/time functions using the SET time_zone = ... query that requinix showed you.

 

------------

 

you should store the date/times as GMT. then if the timezone database that mysql is using is up to date, the mysql convert_tz() function should take into account the DST start/stop dates of the timezones you put into it.

Edited May 23, 2013 by mac_gyver

[mastubbs]

Hi thanks for the help,

So i tried to set both php and mysql to use London/Europe by:

<?php
$connect = mysql_connect("localhost","jasperss_par1","password");
if (!$connect)
{
die("MySQL could not connect!");
}

$DB = mysql_select_db('jasperss_par1pats');

if(!$DB)
{
die("MySQL could not select Database!");
}

date_default_timezone_set('Europe/London');
mysql_query("SET time_zone = 'Europe/London'");

if (date_default_timezone_get()) {
    echo 'date_default_timezone_set: ' . date_default_timezone_get() . '<br />';
}

if (ini_get('date.timezone')) {
    echo 'date.timezone: ' . ini_get('date.timezone');
}

?>

but this returns :

date_default_timezone_set: Europe/London
date.timezone: America/New_York

Should it not return:

date_default_timezone_set: Europe/London
date.timezone: Europe/London

?? Maybe i cant change it from New York time for some reason?

 

you should store the date/times as GMT. then if the timezone database that mysql is using is up to date, the mysql convert_tz() function should take into account the DST start/stop dates of the timezones you put into it.

 

Assuming for now i have to store the datetimes as new york time, i should still be able to return these times as london time right? I tried this but i get a syntax error, am i using it wrongly?

<?php
$connect = mysql_connect("localhost","jasperss_par1","password");
if (!$connect)
{
die("MySQL could not connect!");
}
 
$DB = mysql_select_db('jasperss_par1pats');
 
if(!$DB)
{
die("MySQL could not select Database!");
}
 
if(isset($_GET['mrn']))
{
 
$Search = $_GET['mrn'];
$Find_Query1 = mysql_query("SELECT CONVERT_TZ('_sfm_form_submision_time_','America/New_York','Europe/London') DATE_FORMAT(_sfm_form_submision_time_,'%H:%i') AS time, SBP FROM obs WHERE mrn='$Search' AND DATE(_sfm_form_submision_time_) = CURDATE() order by time ASC");
if(!$Find_Query1)
{
die(mysql_error());
}
 
while($row = mysql_fetch_assoc($Find_Query1))
{
    echo '<br/> TIME: '.$row['time'];
    echo '<br/> SBP: '.$row['SBP'];
echo '<br/>';
 
}
$numCount = mysql_num_rows($Find_Query1);
 
if ($numCount < 1) 
{
print("no sbp found for that mrn today");
}
}
?>

Thanks again for the help.

Edited May 24, 2013 by mastubbs