One hour incorrect in Date() - DST problem?

[Claes100]

Dear all,
I would like to show the time remaining for different online tests but I receive a 1 hour difference (1 more) from the correct value. Can it be a DST problem or something I completely missed...?

$startTime = "2013-06-20 10:00:00"; // received from MySql
$timeLim = 4; // hours to complete test
$secondsLeft = strtotime($startTime) + $timeLim*60*60 - time();
$timeLeft = date("H:i:s", $secondsLeft);
echo $timeLeft;

The above gives me for example 03:20:10 left to complete the test, but the actual should be 02:20:10...

Any comment much appreciated.
Kind Regards
/Claes, Stockholm, Sweden

[phranque]

i would examine the value of time() to see if you are getting an expected result.
perhaps your server is located in another time zone or has the default time zone set incorrectly?

this might be helpful.
PHP: date_default_timezone_get:
http://www.php.net/manual/en/function.date-default-timezone-get.php [php.net]

[swa66]

Dealing with time:

I'm a firm believer of always using UTC (or GMT for those inclined to call it that) internally.And only use local time when dealing with users that you know the zimezone they're in for.

As far as mysql goes, I also believe it's better to translate the stored time into unix time and then process it instead of using pattern recognition - which is inherently tricky- as done in strtotime().

So instead of
SELECT field ...
use
SELECT UNIX_TIMESTAMP(field) ...

that way you get seconds since Jan 1st, 1970 as a number.

Also: it's not just php that knows about timezones, mysql does too!
e.g.: [dev.mysql.com...]

[swa66]

Maybe a mysql level example would help:

there is a global timezone and a per session time zone.
to see their current setting:

mysql> SELECT @@global.time_zone, @@session.time_zone;
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| SYSTEM             | SYSTEM              |
+--------------------+---------------------+
1 row in set (0.02 sec)

To see the time in local time:

mysql> SELECT NOW();
+---------------------+
| now()               |
+---------------------+
| 2013-06-20 16:53:49 |
+---------------------+
1 row in set (0.00 sec)

to switch the per session timezone to UTC:

mysql> set time_zone='+00:00';
Query OK, 0 rows affected (0.00 sec)

This makes the output of NOW() in UTC:

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2013-06-20 14:54:18 |
+---------------------+
1 row in set (0.00 sec)

To switch back the system timezone:

mysql> set time_zone=SYSTEM;
Query OK, 0 rows affected (0.00 sec)

UNIX_TIMESTAMP() is however impervious to timezones. e.g.:

mysql> set time_zone='+00:00';
Query OK, 0 rows affected (0.00 sec)

mysql> select unix_timestamp(now());
+-----------------------+
| unix_timestamp(now()) |
+-----------------------+
|            1371740403 |
+-----------------------+
1 row in set (0.00 sec)

mysql> set time_zone=SYSTEM;
Query OK, 0 rows affected (0.00 sec)

mysql> select unix_timestamp(now());
+-----------------------+
| unix_timestamp(now()) |
+-----------------------+
|            1371740422 |
+-----------------------+
1 row in set (0.00 sec)

mysql stores TIMESTAMP fields in UTC, calling their value with UNIX_TIMESTAMP(field), results in no conversions and as such guarantees no trouble from the overlapping due to turning back the clock once a year.

[g1smd]

An event that happens at 2pm in London occurs one hour before an event that happens at 10am in New York.

Using UTC is vital for web applications.

[DrDoc]

An event that happens at 2pm in London occurs one hour before an event that happens at 10am in New York.

... except for about a week every spring and fall, since the U.S. and U.K. aren't necessarily switching DST at the same time.

[Claes100]

Thanks for your input, will do some testing during the week! /Claes