Why do PHP and MySQL unix timestamps diverge on 1983-10-29?

Question

I've been using PHP's strtotime and MySQL's UNIX_TIMESTAMP functions in my app, to convert dates into timestamps. PHP and MySQL are running on my local machine, and these functions generally return the same result, as I expect them to:

$ php <?php echo strtotime("2011-06-02"); ?> 1307001600  mysql> SELECT UNIX_TIMESTAMP("2011-06-02") ts; +------------+ | ts         | +------------+ | 1307001600 | +------------+ 

But, sorta by chance, I happened to notice that when I entered 1983-01-01 as the date, the results were no longer equal:

$ php <?php echo strtotime("1983-01-01"); ?> 410263200  mysql> SELECT UNIX_TIMESTAMP("1983-01-01") ts; +-----------+ | ts        | +-----------+ | 410256000 | +-----------+ 

As you can see, PHP returned 410263200, while MySQL returned 410256000 - a difference of 7200 seconds.

This got me curious, and I wanted to know on what date the timestamps were no longer equivalent, so I wrote a little program that starts with today's date (in Y-m-d format), uses PHP's strtotime and MySQL's UNIX_TIMESTAMP and compares the results. It then subtracts 1 day from each value and loops until they're no longer equal.

The result:

1983-10-29

On October 29, 1983, for some reason, strtotime and UNIX_TIMESTAMP return values that differ by 7200 seconds.

Any ideas?

Thanks for reading.

Answer 1

You say you're on Alaskan time. From http://www.statoids.com/tus.html:

1983-10-30 02:00: All parts of Alaska except the Aleutians and Saint Lawrence Island switched to AT. Prior to the change, Alaska east of 138°W (Juneau) had been on PT; between 138°W and 141°W (Yakutat) had been on Yukon Time, which was UTC-9 with DST; west of 162°W (Nome) had been on Bering Time, which was UTC-11 with DST. Name of Alaska-Hawaii zone changed to Hawaii-Aleutian.

So, I'd guess that this is because your timezone changed by two hours (7200 seconds = 2 hours) on 30th October 1983.

I'd guess that MySQL's UNIX_TIMESTAMP is using a different timezone from your Alaskan setting in PHP, which may be either the server default, or dependent on your connection settings, so these diverge when you hit that date.

You may be able to glean more information by asking MySQL what its timezone setting is on that connection with SELECT @@global.time_zone, @@session.time_zone;; see this answer for more info on the output and how to interpret it.