29

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.

echo
  • 7,737
  • 2
  • 35
  • 33
  • 3
    What timezone are you in? (`date_default_timezone_get()`)? Wonder if it's some odd calendar change specific to a timezone -- presumably that happened on 30 October 1983. – Matt Gibson Jun 02 '11 at 14:21
  • 2
    For two hours, the world stopped so we could all play our ColecoVision games on the newly released "Adam" – AJ. Jun 02 '11 at 14:25

1 Answers1

22

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.

Community
  • 1
  • 1
Matt Gibson
  • 37,886
  • 9
  • 99
  • 128
  • Nice find...so it is reporting accurately. – AJ. Jun 02 '11 at 14:26
  • 1
    Also a nice [read](http://www.alaskahistoricalsociety.org/index.cfm/discover-alaska/Glimpses-of-the-Past/98) Especially since it documents that in 1883 (100 years earlier) timezones were set for the US, except Alaska, since it had no railroads. – Mel Jun 02 '11 at 14:29
  • Yup. I'd guess that MySQL's UNIX_TIMESTAMP is using a different timezone, which may be either the server default, or dependant on your connection settings, so this diverges from PHP's Alaskan timezone when you hit the changeover date. – Matt Gibson Jun 02 '11 at 14:29
  • MySQL returns SYSTEM for both values, and PHP reports US/Alaska. Date & Time Preferences (OSX) says AKDT. – echo Jun 02 '11 at 14:44
  • @echo I'm guessing they're still not using the same zone information, though. Interestingly, on my install of MySQL on OSX, if I set my timezone to Alaska, I get 410263200 as the result of `UNIX_TIMESTAMP("1983-01-01") ts`! This is probably dependent on all sorts of things, including whether you've loaded the system zone file information (using the output of `mysql_tzinfo_to_sql`), etc. – Matt Gibson Jun 02 '11 at 15:11
  • Oh interesting... I'll do some more digging this evening, and see what i can determine. Will post findings! – echo Jun 02 '11 at 15:17