PHP uses Savings Time, MySQL does not

Together they lose an hour

[Amarsir]

My site needs to be able to mark an event X hours in the future, then indicate how much time is left until that event, and ultimately note when it's passed.  There are other considerations but that's the gist of the current concern.

I use ADDDATE in and strtotime() out.  Straightforward except for daylight savings time.  Test code like this:

$setSQL = "
  UPDATE Test_Table SET FutureTime = ADDDATE(now(), INTERVAL 1334 HOUR)
  WHERE UserID = 0";
$Result1 = mysql_query($setSQL) or die(mysql_error());

$query_Check = "
  SELECT FutureTime,
    now() as Current
  FROM Test_Table
  WHERE UserID = 0";
$check = mysql_query($query_Check) or die(mysql_error());
$row_check = mysql_fetch_assoc($check);

$then = strtotime($row_check['FutureTime']);
$now = strtotime($row_check['Current']);

echo "difference is " . ($then - $now) / 60 / 60;

And that clearly demonstrates the problem because I set interval of 1334 but get back 1333.

Now broadly speaking I suspect there are a few ways to solve this.  But I'm not knowledgeable enough to weigh them.  So I would appreciate some advice on how you would fix this contradiction.

As I say there are a number of variations on this.  Occasionally I want to compare to a manually-set system event and so I use mktime() without a db call, and it would be nice if the method here and the method there were compatible.  But perhaps not essential?

Anyway, whatever you think would be the simplest /  most flexible solution I'd appreciate hearing.  If it matters, the server in question is a VPS so I believe I can control system time but not hardware time.

[coopster]

Either set the timezone in PHP before calling strtotime or better yet do the calculations in the query statement instead.

[Amarsir]

Thanks.

That's sort of how I was leaning, but help my reasoning on this please:

If I do all the calculations in MySQL, it will be consistent. But what happens when we get up to that DST hour? It seems to me that the clock would jump ahead and we'll lose an hour anyway, since MySQL never adjusted for it.

And then in the fall, it would back the clock up and re-count that hour. On both occasions it will ultimately mark the wrong time, because MySQL's ADDDATE() doesn't adjust for DST, but it is ultimately affected by it when the server clock changes.

Which leads me to conclude that only PHP is correct and I should do all the calculations there instead. But to my knowledge none of the PHP functions are as simple as ADDDATE, and then I have to give special concern to formatting.

[coopster]

Why not just use UTC and adjust when necessary? If not, then set your servers and services to use the same time zone. MySQL Server Time Zone Support [dev.mysql.com]

[Amarsir]

Now that has promise and is something I didn't know about. Thanks much.