MySQL daylight savings info seems to be incorrect. How can I ensure it is up to date in Windows?

I found that a bug in my program was caused by MySQL's UNIX_TIMESTAMP() function returning a different value to PHP's strtotime() when given the same input.

I then found that the date on which the bug occurred happened to be the date on which daylight savings comes into effect in my timezone.

So I need to know why PHP knows about the daylight savings start day, but MySQL does not. Shouldn't both PHP and MySQL get their timezone and daylight savings info from the operating system?

The operating system is Windows 7, and according to the Windows Update program, it is up to date with patches.

Here is my code:

// Checking timezones in MySQL and PHP.
echo "<pre>\n";
echo "date_default_timezone_get(): " . date_default_timezone_get() . "\n";
echo "date('P'): " . date('P') . "\n";
echo "MySQL timezone: " . $sqlObj->fetchField('SELECT IF(@@session.time_zone="SYSTEM", @@system_time_zone, @@session.time_zone)') . "\n";

// Testing MySQL and PHP conversion to unix timestamp for specific datetime values.
for ($hour = 0; $hour < 5; ++$hour)
{
   $timeString         = '2018-10-07 ' . (($hour < 10)? '0': '') . "$hour:00:00";
   $timestampFromMySql = $sqlObj->fetchField('SELECT UNIX_TIMESTAMP(?)', array($timeString));
   $timestampFromPhp   = strtotime($timeString);
   echo " * timestring        : $timeString\n";
   echo "   timestampFromMySql: $timestampFromMySql\n";
   echo "   timestampFromPhp  : $timestampFromPhp\n";

   // Checking daylight savings status for the date in question in PHP.
   echo "   date('I', \$timestampFromMySql): " . date('I', $timestampFromMySql) . "\n";
   echo "   date('I', \$timestampFromPhp  ): " . date('I', $timestampFromPhp  ) . "\n";

   echo "   difference: " . ($timestampFromMySql - $timestampFromPhp) . "\n";
}

echo "</pre>\n";

Here is the output. Notice that a 3600 second or one hour difference appears in unix times reported by PHP and MySQL at 3am.

date_default_timezone_get(): Australia/Melbourne
date('P'): +10:00
MySQL timezone: +10:00
 * timestring        : 2018-10-07 00:00:00
   timestampFromMySql: 1538834400
   timestampFromPhp  : 1538834400
   date('I', $timestampFromMySql): 0
   date('I', $timestampFromPhp  ): 0
   difference: 0
 * timestring        : 2018-10-07 01:00:00
   timestampFromMySql: 1538838000
   timestampFromPhp  : 1538838000
   date('I', $timestampFromMySql): 0
   date('I', $timestampFromPhp  ): 0
   difference: 0
 * timestring        : 2018-10-07 02:00:00
   timestampFromMySql: 1538841600
   timestampFromPhp  : 1538841600
   date('I', $timestampFromMySql): 1
   date('I', $timestampFromPhp  ): 1
   difference: 0
 * timestring        : 2018-10-07 03:00:00
   timestampFromMySql: 1538845200
   timestampFromPhp  : 1538841600
   date('I', $timestampFromMySql): 1
   date('I', $timestampFromPhp  ): 1
   difference: 3600
 * timestring        : 2018-10-07 04:00:00
   timestampFromMySql: 1538848800
   timestampFromPhp  : 1538845200
   date('I', $timestampFromMySql): 1
   date('I', $timestampFromPhp  ): 1
   difference: 3600

Google tells me that daylight saving time 2018 in Melbourne will begin at 2:00 am on Sunday, 7 October, and clocks will go forward one hour. So 2am on 7 October 2018 is in fact the same time as 3am on 7 October 2018, and so the behaviour I observe from PHP in the script output above is expected. MySQL's behaviour appears to be incorrect.

Can anyone tell me how to ensure that MySQL has up to date information relating to daylight savings?

Also I found that the mysql.time_zone tables are all empty. Does this mean that MySQL has no timezone information, or does it mean that MySQL's timezone information is coming from the operating system?

Solution

The relevant documentation is here: https://dev.mysql.com/doc/refman/5.5/en/time-zone-upgrades.html

The key sentences are:

"The operating system time affects the value that the MySQL server uses for times if its time zone is set to SYSTEM"

and

"If you use named time zones with MySQL, make sure that the time zone tables in the mysql database are up to date."

Later the documentation states that if the mysql.time_zone_name table is empty, then no one can be using named time zones, and you don't need to update the tables.

So, to force MySQL to use operating system information for time zones and daylight savings periods, set the timezone to 'SYSTEM'.

Also, if the timezone is set to 'SYSTEM' you do not need to fill the mysql.time_zone tables.

After setting the MySQL time zone to 'SYSTEM' by running 'SET time_zone="SYSTEM"', I re-ran the script from the question and got the following output, which shows PHP and MySQL behaving the same way as expected.

date_default_timezone_get(): Australia/Melbourne
date('P'): +10:00
MySQL timezone: AUS Eastern Standard Time
 * timestring        : 2018-10-07 00:00:00
   timestampFromMySql: 1538834400
   timestampFromPhp  : 1538834400
   date('I', $timestampFromMySql): 0
   date('I', $timestampFromPhp  ): 0
   difference: 0
 * timestring        : 2018-10-07 01:00:00
   timestampFromMySql: 1538838000
   timestampFromPhp  : 1538838000
   date('I', $timestampFromMySql): 0
   date('I', $timestampFromPhp  ): 0
   difference: 0
 * timestring        : 2018-10-07 02:00:00
   timestampFromMySql: 1538841600
   timestampFromPhp  : 1538841600
   date('I', $timestampFromMySql): 1
   date('I', $timestampFromPhp  ): 1
   difference: 0
 * timestring        : 2018-10-07 03:00:00
   timestampFromMySql: 1538841600
   timestampFromPhp  : 1538841600
   date('I', $timestampFromMySql): 1
   date('I', $timestampFromPhp  ): 1
   difference: 0
 * timestring        : 2018-10-07 04:00:00
   timestampFromMySql: 1538845200
   timestampFromPhp  : 1538845200
   date('I', $timestampFromMySql): 1
   date('I', $timestampFromPhp  ): 1
   difference: 0