Datetime to timestamp and back in GMT? 1

[Dweezel]

I've got into a problem that I can't seem to crack through my usual route of google,php manual,forum/book search. I'm storing a date in mysql datetime format. One of the many things that I need this datetime information for is as follows:

I need to be able to fetch the datetime field from the browser (the rows of data in this field are in 15 minute intervals) which are stored in GMT. I then need to be able to convert this date from mysql datetime to a timestamp, do some calculations (adding and subtracting from the timestamp generally), and then convert the timestamp back to a MySql GMT datetime string.

So the process is basically this:

Datetime(GMT) > Timestamp > Datetime(GMT).

You would think that this would be a fairly simple procedure, but I'm having terrible trouble finding a way of doing these conversions and keeping the time in GMT at all stages.

The server I'm using is set to BST ( british summer time) I've tried all of the relevant PHP date/time functions and can't seem to get any of them to keep the date/time in GMT during BST hours. There are knew functions in PHP 5 that help with this kind of calculation apparently, but this is of no use to me unfortunately, as I'm on a remote server running PHP version 4.3.10.

Any help on this would be much appreciated.

 

[Dweezel]

I just re-read paragraph two and this:

I need to be able to fetch the datetime field from the browser

should be this:

I need to be able to fetch the datetime field from the database

So to recap:

2006-03-26 01:00:00(GMT) > Timestamp(GMT) > 2006-03-26 01:00:00(GMT)

Is this possible with a server set to BST?

 

[jpadie]

for your scripts you might want to use

date_default_timezone_set("GMT+0");

at the start of the script. then all of the subsequent date time calcs are done on GMT + 0.

strtotime is timezone independent and is based number of seconds since GMT.

 

[Dweezel]

Thanks jpadie. Unfortunatley date_default_timezone_set() is only available on PHP version 5. The remote server I'm using is running version 4.3.10.

I was using strtotime() in some of my attempts to get this to work and I also expected it to stick to GMT. However, strange things seem to happen during the change over from GMT to BST on April the 26th.

I tried the following ( where '$get_data_result' is my result array from a database query ):

for($i=0; $i < $get_data_numrows; $i++)
{
 
 $row = mysql_fetch_array($get_data_result);

 $datetime_string = $row['stamp'];[COLOR=red] //datetime string from database (GMT).[/color]

 $timestamp = strtotime($datetime_string); [COLOR=red] //converts datetime_string above to timestamp. [/color]

 $back_to_string = gmdate('Y-m-d H:i:s',$timestamp); [COLOR=red] // converts timestring back to datestring.[/color]
 

 echo $datetime_string." ---- ".$timestamp." --- ".$back_to_string."<br>";
}

The result is below:

[b]   $datetime_string     $timestamp     $back_to_string[/b]
[b]1[/b]  2006-03-26 00:00:00 ---- 1143331200 --- 2006-03-26 00:00:00
[b]2[/b]  2006-03-26 00:15:00 ---- 1143332100 --- 2006-03-26 00:15:00
[b]3[/b]  2006-03-26 00:30:00 ---- 1143333000 --- 2006-03-26 00:30:00
[b]4[/b]  2006-03-26 00:45:00 ---- 1143333900 --- 2006-03-26 00:45:00
[b]5[/b]  2006-03-26 01:00:00 ---- 1143334800 --- 2006-03-26 02:00:00
[b]6[/b]  2006-03-26 01:15:00 ---- 1143335700 --- 2006-03-26 02:15:00
[b]7[/b]  2006-03-26 01:30:00 ---- 1143336600 --- 2006-03-26 02:30:00
[b]8[/b]  2006-03-26 01:45:00 ---- 1143337500 --- 2006-03-26 02:45:00
[b]9[/b]  2006-03-26 02:00:00 ---- 1143334800 --- 2006-03-26 02:00:00
[b]10[/b] 2006-03-26 02:15:00 ---- 1143335700 --- 2006-03-26 02:15:00
[b]11[/b] 2006-03-26 02:30:00 ---- 1143336600 --- 2006-03-26 02:30:00
[b]12[/b] 2006-03-26 02:45:00 ---- 1143337500 --- 2006-03-26 02:45:00
[b]13[/b] 2006-03-26 03:00:00 ---- 1143338400 --- 2006-03-26 03:00:00
[b]14[/b] 2006-03-26 03:15:00 ---- 1143339300 --- 2006-03-26 03:15:00

Can you see what is happening to the $back_to_string at 1am (result 5) and the timestamp at 2am (result 9). The timestamp should be going up by 900 (15 minutes) at each iteration. It loses 2700 (1 hour) at 2am.

This is the result I'm trying to achieve:

[b]   $datetime_string     $timestamp     $back_to_string[/b]
[b]1[/b]  2006-03-26 00:00:00 ---- 1143331200 --- 2006-03-26 00:00:00
[b]2[/b]  2006-03-26 00:15:00 ---- 1143332100 --- 2006-03-26 00:15:00
[b]3[/b]  2006-03-26 00:30:00 ---- 1143333000 --- 2006-03-26 00:30:00
[b]4[/b]  2006-03-26 00:45:00 ---- 1143333900 --- 2006-03-26 00:45:00
[b]5[/b]  2006-03-26 01:00:00 ---- 1143334800 --- 2006-03-26 01:00:00
[b]6[/b]  2006-03-26 01:15:00 ---- 1143335700 --- 2006-03-26 01:15:00
[b]7[/b]  2006-03-26 01:30:00 ---- 1143336600 --- 2006-03-26 01:30:00
[b]8[/b]  2006-03-26 01:45:00 ---- 1143337500 --- 2006-03-26 01:45:00
[b]9[/b]  2006-03-26 02:00:00 ---- 1143338400 --- 2006-03-26 02:00:00
[b]10[/b] 2006-03-26 02:15:00 ---- 1143339300 --- 2006-03-26 02:15:00
[b]11[/b] 2006-03-26 02:30:00 ---- 1143340200 --- 2006-03-26 02:30:00
[b]12[/b] 2006-03-26 02:45:00 ---- 1143341100 --- 2006-03-26 02:45:00
[b]13[/b] 2006-03-26 03:00:00 ---- 1143342000 --- 2006-03-26 03:00:00
[b]14[/b] 2006-03-26 03:15:00 ---- 1143342900 --- 2006-03-26 03:15:00

Thanks for your help jpadie. Any further advice would be much appreciated.

 

[jpadie]

aha.
bit of a kludge but try this.

for($i=0; $i < $get_data_numrows; $i++)
{
 
 $row = mysql_fetch_array($get_data_result);

 $datetime_string = $row['stamp']; //datetime string from database (GMT).

 $timestamp = strtotime($datetime_string); //converts datetime_string above to timestamp. 
 
  $tmp = localtime($timestamp, true);
  if ($tmp['tm_isdst'] === 1):
    $timestamp = strtotime("-1 hour", $timestamp);
  endif;

 $back_to_string = gmdate('Y-m-d H:i:s',$timestamp); // converts timestring back to datestring.
 

 echo $datetime_string." ---- ".$timestamp." --- ".$back_to_string."<br>";
}

 

[jpadie]

oops

ignore previous post - i reused a variable incorrectly,
try this, instead.

for($i=0; $i < $get_data_numrows; $i++)
{
 
 $row = mysql_fetch_array($get_data_result);

 $datetime_string = $row['stamp']; //datetime string from database (GMT).

 $timestamp = strtotime($datetime_string); //converts datetime_string above to timestamp. 
 
  $tmp = localtime($timestamp, true);
  if ($tmp['tm_isdst'] === 1):
    $ttimestamp = strtotime("-1 hour", $timestamp);
  endif;

 $back_to_string = gmdate('Y-m-d H:i:s',$ttimestamp); // converts timestring back to datestring.
 

 echo $datetime_string." ---- ".$timestamp." --- ".$back_to_string."<br>";
}

 

[Dweezel]

Thanks again for your time on this jpadie.

I turned your last example into a self contained script, using an array and a foreach loop. It gives a rather odd set of results. If you copy and paste you can try this out for yourself.

Not entirely sure what's happening in terms of BST/GMT here:

<?php

$datestring_array = array('2006-03-26 00:00:00','2006-03-26 00:15:00','2006-03-26 00:30:00','2006-03-26 00:45:00','2006-03-26 01:00:00','2006-03-26 01:15:00','2006-03-26 01:30:00','2006-03-26 01:45:00','2006-03-26 02:00:00','2006-03-26 02:15:00','2006-03-26 02:30:00','2006-03-26 02:45:00','2006-03-26 03:00:00','2006-03-26 03:15:00');



foreach($datestring_array as $d_string)
{
 

 $timestamp = strtotime($d_string); [COLOR=red] //converts datetime_string above to timestamp. [/color]
 
  $tmp = localtime($timestamp, true);
  if ($tmp['tm_isdst'] === 1):
    $ttimestamp = strtotime("-1 hour", $timestamp);
  endif;

 $back_to_string = gmdate('Y-m-d H:i:s',$ttimestamp); [COLOR=red] // converts timestring back to datestring. [/color]
 

 echo $d_string." ---- ".$timestamp." --- ".$back_to_string."<br>";
}

?>

RESULTS:

[b]   $datetime_string     $timestamp     $back_to_string[/b]
[b]1[/b]  2006-03-26 00:00:00 ---- 1143331200 --- 2006-03-26 22:00:00
[b]2[/b]  2006-03-26 00:15:00 ---- 1143332100 --- 2006-03-26 22:00:00
[b]3[/b]  2006-03-26 00:30:00 ---- 1143333000 --- 2006-03-26 22:00:00
[b]4[/b]  2006-03-26 00:45:00 ---- 1143333900 --- 2006-03-26 22:00:00
[b]5[/b]  2006-03-26 01:00:00 ---- 1143334800 --- 2006-03-26 00:00:00
[b]6[/b]  2006-03-26 01:15:00 ---- 1143335700 --- 2006-03-26 00:15:00
[b]7[/b]  2006-03-26 01:30:00 ---- 1143336600 --- 2006-03-26 00:30:00
[b]8[/b]  2006-03-26 01:45:00 ---- 1143337500 --- 2006-03-26 00:45:00
[b]9[/b]  2006-03-26 02:00:00 ---- 1143334800 --- 2006-03-26 00:00:00
[b]10[/b] 2006-03-26 02:15:00 ---- 1143335700 --- 2006-03-26 00:15:00
[b]11[/b] 2006-03-26 02:30:00 ---- 1143336600 --- 2006-03-26 00:30:00
[b]12[/b] 2006-03-26 02:45:00 ---- 1143337500 --- 2006-03-26 00:45:00
[b]13[/b] 2006-03-26 03:00:00 ---- 1143338400 --- 2006-03-26 01:00:00
[b]14[/b] 2006-03-26 03:15:00 ---- 1143339300 --- 2006-03-26 01:15:00

I'm guessing that functions such as date_default_timezone_set() that you mentioned in an earlier reply have been introduced in PHP5 to overcome problems such as this.

I hope there is a solution for my platform though. Any other ideas?

 

[jpadie]

can you change this:

  if ($tmp['tm_isdst'] === 1):
    $ttimestamp = strtotime("-1 hour", $timestamp);
  endif;

to

  if ($tmp['tm_isdst'] === 1):
    $ttimestamp = strtotime("-1 hour", $timestamp);
  else:
    $ttimestamp = $timestamp;
  endif;

remember that the initial conversion to the timestamp will assume that the formatted datetime is at the local datetime. you might need to adjust for that too. can you always be certain that the db time is in GMT (unadjusted for GMT)?

 

[jpadie]

Dweezel

i'm being too dim for words. the problem was always in the input date.

the following code solves the issue by telling strtotime that the incoming formatted date is in GMT.

<?php

$datestring_array = array('2006-03-26 00:00:00','2006-03-26 00:15:00','2006-03-26 00:30:00','2006-03-26 00:45:00','2006-03-26 01:00:00','2006-03-26 01:15:00','2006-03-26 01:30:00','2006-03-26 01:45:00','2006-03-26 02:00:00','2006-03-26 02:15:00','2006-03-26 02:30:00','2006-03-26 02:45:00','2006-03-26 03:00:00','2006-03-26 03:15:00');
foreach($datestring_array as $d_string)
{
	$timestamp = strtotime($d_string ." GMT"); //converts datetime_string above to timestamp.
	$back_to_string = gmdate('Y-m-d H:i:s',$timestamp); // converts timestring back to datestring.
	echo $d_string." ---- ".$timestamp." --- ".$back_to_string."<br>";
}
?>

this produces the following results

2006-03-26 00:00:00 ---- 1143331200 --- 2006-03-26 00:00:00
2006-03-26 00:15:00 ---- 1143332100 --- 2006-03-26 00:15:00
2006-03-26 00:30:00 ---- 1143333000 --- 2006-03-26 00:30:00
2006-03-26 00:45:00 ---- 1143333900 --- 2006-03-26 00:45:00
2006-03-26 01:00:00 ---- 1143334800 --- 2006-03-26 01:00:00
2006-03-26 01:15:00 ---- 1143335700 --- 2006-03-26 01:15:00
2006-03-26 01:30:00 ---- 1143336600 --- 2006-03-26 01:30:00
2006-03-26 01:45:00 ---- 1143337500 --- 2006-03-26 01:45:00
2006-03-26 02:00:00 ---- 1143338400 --- 2006-03-26 02:00:00
2006-03-26 02:15:00 ---- 1143339300 --- 2006-03-26 02:15:00
2006-03-26 02:30:00 ---- 1143340200 --- 2006-03-26 02:30:00
2006-03-26 02:45:00 ---- 1143341100 --- 2006-03-26 02:45:00
2006-03-26 03:00:00 ---- 1143342000 --- 2006-03-26 03:00:00
2006-03-26 03:15:00 ---- 1143342900 --- 2006-03-26 03:15:00

[/code]

 

[Dweezel]

Thanks jpadie, but I'm afraid on my server I get these results:

2006-03-26 00:00:00 ---- 1143331200 --- 2006-03-26 00:00:00
2006-03-26 00:15:00 ---- 1143332100 --- 2006-03-26 00:15:00
2006-03-26 00:30:00 ---- 1143333000 --- 2006-03-26 00:30:00
2006-03-26 00:45:00 ---- 1143333900 --- 2006-03-26 00:45:00
2006-03-26 01:00:00 ---- 1143338400 --- 2006-03-26 02:00:00
2006-03-26 01:15:00 ---- 1143339300 --- 2006-03-26 02:15:00
2006-03-26 01:30:00 ---- 1143340200 --- 2006-03-26 02:30:00
2006-03-26 01:45:00 ---- 1143341100 --- 2006-03-26 02:45:00
2006-03-26 02:00:00 ---- 1143338400 --- 2006-03-26 02:00:00
2006-03-26 02:15:00 ---- 1143339300 --- 2006-03-26 02:15:00
2006-03-26 02:30:00 ---- 1143340200 --- 2006-03-26 02:30:00
2006-03-26 02:45:00 ---- 1143341100 --- 2006-03-26 02:45:00
2006-03-26 03:00:00 ---- 1143342000 --- 2006-03-26 03:00:00
2006-03-26 03:15:00 ---- 1143342900 --- 2006-03-26 03:15:00

Is your server set to GMT?

You'll notice that once again it's the strtotime() function that seems to be creating problems. It's not giving a GMT result even when specifying that the input is GMT.

To answer your previous question the initial mysql datetimes will always be in GMT. The data is collected from remote monitoring equipment and passed to my computer via SMS daily. The measurements are always taken at 15 minute intervals and the time of the measurements is always logged in GMT.

 

[jpadie]

when i ran the test i had set my server to gmt. i have also rerun the test with the server set to CET.

both give me the same result (i.e. the one that you posted as desired above).

i am using IIS. i can try and replicate the behaviour on an apache server if this is what you are using.

just to be sure that i am correctly replicating the issue can you post back the code you are using, please?

 

[Dweezel]

Thanks a lot. I'm running on apache with PHP 4.3.10.

Here's the code I'm using:

<?php

$datestring_array = array('2006-03-26 00:00:00','2006-03-26 00:15:00','2006-03-26 00:30:00','2006-03-26 00:45:00','2006-03-26 01:00:00','2006-03-26 01:15:00','2006-03-26 01:30:00','2006-03-26 01:45:00','2006-03-26 02:00:00','2006-03-26 02:15:00','2006-03-26 02:30:00','2006-03-26 02:45:00','2006-03-26 03:00:00','2006-03-26 03:15:00');


foreach($datestring_array as $d_string)
{
    $timestamp = strtotime($d_string ." GMT"); //converts datetime_string above to timestamp.
    $back_to_string = gmdate('Y-m-d H:i:s',$timestamp); // converts timestring back to datestring.
    echo $d_string." ---- ".$timestamp." --- ".$back_to_string."<br>";
}
?>

Thanks again.

 

[Dweezel]

I've cracked it jpadie!

I've made my way through the entire set of PHP date and time functions to try and get something to solve this problem with my set up, and I'm pleased to say that I've finally got something to work.

It turns out that mktime with a final argument set to zero(representing GMT) works fine. If I wasn't running such an old install of PHP(or if I was using IIS perhaps) I'm sure your solution would have worked for me to. Thanks a lot for your time jpadie.

Here's my final code:

$datestring_array = array('2006-03-26 00:00:00','2006-03-26 00:15:00','2006-03-26 00:30:00','2006-03-26 00:45:00','2006-03-26 01:00:00','2006-03-26 01:15:00','2006-03-26 01:30:00','2006-03-26 01:45:00','2006-03-26 02:00:00','2006-03-26 02:15:00','2006-03-26 02:30:00','2006-03-26 02:45:00','2006-03-26 03:00:00','2006-03-26 03:15:00');


foreach($datestring_array as $d_string)
{
    $split_string = explode(' ',$d_string);
	$date_array = explode('-',$split_string[0]);
	$time_array = explode(':',$split_string[1]);
	
	$day = $date_array[2];
	$month = $date_array[1];
	$year = $date_array[0];
	
	$hour = $time_array[0];
	$minute = $time_array[1];
	$second = $time_array[2];
	
	
	
	
	
    $timestamp = mktime($hour,$minute,$second,$month,$day,$year,0);
    $back_to_string = gmdate('Y-m-d H:i:s',$timestamp); // converts timestring back to datestring.
    echo $d_string." ---- ".$timestamp." --- ".$back_to_string."<br>";
}

 

[jpadie]

i have a much easier solution that runs on debian, apache and php4.

try this (back to your original code with one tiny mod):

<?php
putenv("TZ=GMT"); //this is the mod
$datestring_array = array('2006-03-26 00:00:00','2006-03-26 00:15:00','2006-03-26 00:30:00','2006-03-26 00:45:00','2006-03-26 01:00:00','2006-03-26 01:15:00','2006-03-26 01:30:00','2006-03-26 01:45:00','2006-03-26 02:00:00','2006-03-26 02:15:00','2006-03-26 02:30:00','2006-03-26 02:45:00','2006-03-26 03:00:00','2006-03-26 03:15:00');

foreach($datestring_array as $d_string)
{
	$timestamp = strtotime($d_string); //converts datetime_string above to timestamp.
	$back_to_string = gmdate('Y-m-d H:i:s',$timestamp); // converts timestring back to datestring.
	echo $d_string." ---- ".$timestamp." --- ".$back_to_string."<br>";
	
}
?>

 

[Dweezel]

That works perfectly! The putenv function isn't listed with the date/time functions in the manual, otherwise I'd probably have found it. Thanks a lot for digging that one out for me. I can cut out all of that string splitting rubbish now

Thanks a lot jpadie.