With the coming of spring and just like the Daffodils erupting from the soil, a whole new selection of bugs appear in your code due to the clocks going forward one hour.
My matrix display project needs to refresh from the dataset once in a while and yesterday with the clocks going forward from GMT to BST a bug caused the refresh time to be set behind or at the same time as the current time in UTC resulting in the matrix display continually sending update requests to the server. As you are probably aware, GMT and UTC are the same time, while BST is one hour ahead.
The Problem
Skip to the end for the fix if you are impatient.
With the failure as described above, I quickly found the problem in my PHP code, the DateTime class automatically sets the timezone region to that used in the application, in my case Europe/London, any conversions made are set relative to that timezone. The matrix display uses a Unix Timestamp for all time calculations, I could have written the code on the server side to use Unix Timestamps, but it turns out that I didn’t and now I need to convert between date strings, date objects and timestamp integers.
In the MySQL database I store the date as a DATETIME but with no timezone information, like so:
|
1 2 3 4 5 6 7 8 9 10 |
<?php ini_set('date.timezone', 'Europe/London'); $currentDate = new DateTime('now', new DateTimeZone('UTC')); $nextUpdate = $currentDate->modify('+1 hours'); $nUpdate = $nextUpdate->format('Y-m-d H:i:s'); // convert to string, eg: 2024-04-01 13:28:57 $sql = sprintf("UPDATE jsonCache SET nextUpdate='%s' WHERE id='%s'", $nUpdate, $id); $this->database->exec($sql); ?> |
I then go to retrieve this date from the database, and convert it back to a PHP datetime object, and in this case display it:
|
1 2 3 4 5 6 7 |
$sql = sprintf("SELECT nextUpdate FROM jsonCache WHERE id='%s'", $id); $result = $this->database->fetchSingle($sql); $nextUpdate = DateTime::createFromFormat('Y-m-d H:i:s', $result["nextUpdate"]); echo(sprintf("<pre>%s</pre>", print_r($nextUpdate, true))); $updateTimestamp = $nextUpdate->getTimestamp(); echo("<p>Unix Timestamp: ".$updateTimestamp." (".gmdate("Y-m-d\TH:i:s\Z", $updateTimestamp).")</p>"); |
And the output for this would be:
|
1 2 3 4 5 6 7 |
DateTime Object ( [date] => 2024-04-01 13:28:57.000000 [timezone_type] => 3 [timezone] => Europe/London ) Unix Timestamp: 1711974537 (2024-04-01T12:28:57Z) |
What you can see here is that while the datetime object is correct the timezone is set to Europe/London so when this is converted to a unix timestamp with $nextUpdate->getTimestamp(); an hour is deducted to convert it to UTC.
My first attempt at fixing this (now omitting the database query for clarity):
|
1 2 3 4 5 6 |
$nextUpdate = DateTime::createFromFormat('Y-m-d H:i:s', "2024-04-01 13:28:57"); $nextUpdate->setTimeZone(new DateTimeZone("UTC")); echo(sprintf("<pre>%s</pre>", print_r($nextUpdate, true))); $updateTimestamp = $nextUpdate->getTimestamp(); echo("<p>Unix Timestamp: ".$updateTimestamp." (".gmdate("Y-m-d\TH:i:s\Z", $updateTimestamp).")</p>"); |
made things worse:
|
1 2 3 4 5 6 7 |
DateTime Object ( [date] => 2024-04-01 12:28:57.000000 [timezone_type] => 3 [timezone] => UTC ) Unix Timestamp: 1711974537 (2024-04-01T12:28:57Z) |
While the timezone is now correct the datetime object has also lost an hour.
The Fix
Turns out all that is needed is to set the timezone when the datetime object is created:
|
1 2 3 4 5 |
$nextUpdate = DateTime::createFromFormat('Y-m-d H:i:s', "2024-04-01 13:28:57", setTimeZone(new DateTimeZone("UTC")); echo(sprintf("<pre>%s</pre>", print_r($nextUpdate, true))); $updateTimestamp = $nextUpdate->getTimestamp(); echo("<p>Unix Timestamp: ".$updateTimestamp." (".gmdate("Y-m-d\TH:i:s\Z", $updateTimestamp).")</p>"); |
to give this resulting output:
|
1 2 3 4 5 6 7 |
DateTime Object ( [date] => 2024-04-01 13:28:57.000000 [timezone_type] => 3 [timezone] => UTC ) Unix Timestamp: 1711978137 (2024-04-01T13:28:57Z) |
So setting the timezone after creating the datetime object modifies the time, giving the setTimeZone() function a bit of a misleading name.
Links and Sources
- Unix Timestamp Epoch Converter: https://www.epochconverter.com/
- PHP PDO Database statements: https://www.php.net/manual/en/book.pdo.php