TimeZone Problems!

[AndrewKinghorn]

Hi all, this is absolutely driving me up the wall! Hope you guys can fix this for me.

 

I have a PHP page that connects to a phpMyAdmin database. The database holds the day in "CURRENT_TIMESTAMP" format. My PHP script is as follows: 

// while there are rows to be fetched...
while ($list = mysql_fetch_assoc($result)) {
//Getting day from database.
$datetime = strtotime($list['Day']);

//Sets the default time for the page.
date_default_timezone_set('Europe/Lond…

//This is then converted to a date suiting to me.
$mysqldate = date("l dS F Y H:i", $datetime);

It changes the time accordingly for the first record on my webpage, but then when a new record is added it makes this record have the correct time, whilst the previous record now has the wrong time! For example the correct time for the first record would be 18:35, then if a new record was added this would hold the correct time but the 18:35 time would drop back to say 14:35 and therefore become incorrect.

What am I doing wrong for the PHP to change the first record only but not the rest? Answers in code would be great!

Thanks.

Edited July 7, 2013 by AndrewKinghorn

[kicken]

Changing the timezone is going to affect how strtotime interprets the string given to it.

 

eg:

date_default_timezone_set('America/Los_Angeles');
$time = strtotime('2013-7-1 5:00');
echo date('r', $time).PHP_EOL; //Mon, 01 Jul 2013 05:00:00 -0700

date_default_timezone_set('Europe/London');
echo date('r', $time).PHP_EOL; //Mon, 01 Jul 2013 13:00:00 +0100

$time = strtotime('2013-7-1 5:00');
date_default_timezone_set('Europe/London');
echo date('r', $time).PHP_EOL; //Mon, 01 Jul 2013 05:00:00 +0100

Notice how the second output shows the time actually being converted from America/Los_Angeles to the equivilent time in Europe/London. The third output however shows that now strtotime is treating the initial time string as if it is in the Europe/London timezone so the time stays the same when output.

 

So given your code, the first row's time will be converted from whatever the server's default timezone is, into the Europe/London timezone. All subsequent rows however will be interpreted as if they originate in the Europe/London timezone so they will not be converted.

 

If you want to set a timezone for your pages, it is something you should do near the beginning of the script as part of the initialization work (ie, same place you'd call session_start, connect to the DB, etc).

[AndrewKinghorn]

Changing the timezone is going to affect how strtotime interprets the string given to it.

 

eg:

date_default_timezone_set('America/Los_Angeles');
$time = strtotime('2013-7-1 5:00');
echo date('r', $time).PHP_EOL; //Mon, 01 Jul 2013 05:00:00 -0700

date_default_timezone_set('Europe/London');
echo date('r', $time).PHP_EOL; //Mon, 01 Jul 2013 13:00:00 +0100

$time = strtotime('2013-7-1 5:00');
date_default_timezone_set('Europe/London');
echo date('r', $time).PHP_EOL; //Mon, 01 Jul 2013 05:00:00 +0100

Notice how the second output shows the time actually being converted from America/Los_Angeles to the equivilent time in Europe/London. The third output however shows that now strtotime is treating the initial time string as if it is in the Europe/London timezone so the time stays the same when output.

 

So given your code, the first row's time will be converted from whatever the server's default timezone is, into the Europe/London timezone. All subsequent rows however will be interpreted as if they originate in the Europe/London timezone so they will not be converted.

 

If you want to set a timezone for your pages, it is something you should do near the beginning of the script as part of the initialization work (ie, same place you'd call session_start, connect to the DB, etc).

 

 

 

Thank you very much, I will give this a go and get back to you.

 

I am grateful for your help (I really am), this has been driving me up the wall.

[AndrewKinghorn]

Changing the timezone is going to affect how strtotime interprets the string given to it.

 

eg:

date_default_timezone_set('America/Los_Angeles');
$time = strtotime('2013-7-1 5:00');
echo date('r', $time).PHP_EOL; //Mon, 01 Jul 2013 05:00:00 -0700

date_default_timezone_set('Europe/London');
echo date('r', $time).PHP_EOL; //Mon, 01 Jul 2013 13:00:00 +0100

$time = strtotime('2013-7-1 5:00');
date_default_timezone_set('Europe/London');
echo date('r', $time).PHP_EOL; //Mon, 01 Jul 2013 05:00:00 +0100

Notice how the second output shows the time actually being converted from America/Los_Angeles to the equivilent time in Europe/London. The third output however shows that now strtotime is treating the initial time string as if it is in the Europe/London timezone so the time stays the same when output.

 

So given your code, the first row's time will be converted from whatever the server's default timezone is, into the Europe/London timezone. All subsequent rows however will be interpreted as if they originate in the Europe/London timezone so they will not be converted.

 

If you want to set a timezone for your pages, it is something you should do near the beginning of the script as part of the initialization work (ie, same place you'd call session_start, connect to the DB, etc).

 

 

 

Hey, I have just tried this and it doesn't work.

 

I changed to code and moved the time zone to the top of the page when I am starting a session:

 

<?php

//Starts session for this page.

session_start();

 

//Connect to the online database through the PHP code saved in "databaseconnection.php"

require_once("databaseconnection.php");

 

date_default_timezone_set('Europe/London');

 

?>

 

Still it does not work.

 

Any ideas?

 

Andrew

[ignace]

Show us your table structure from PhpMyAdmin. And what timezone is your MySQL server in?

[AndrewKinghorn]

Show us your table structure from PhpMyAdmin. And what timezone is your MySQL server in?

 

The problem lies with the following field, the field I am using to store the date is called "Day" and the type is "timestamp" and the default is set to "CURRENT_TIMESTAMP".

 

Hope this is of some use?

[jcbones]

IIRC, CURRENT_TIMESTAMP in MySQL is set to UTC time.  This means that you can set the timezone in MYSQL, and it will return the correct time for the for the timezone set in MySQL. DATE and DATETIME will not do that, but return the same date as was put into MySQL. Note that you may have to use timezone offsets by default, unless the timezones have been loaded.
 

Example

 

mysql_query("SET timezone = '-5:00'");
$result = mysql_query("SELECT `timestamp_column` FROM `table` WHERE 1");

 

This will only change the timezone for the connection, which closes at the end of the script.

 

I know this isn't what you asked for, but it adds to the discussion.