Jonathan's Universe

Posts Tagged ‘time zones’

So, I noticed when my employer moved to a new web-server running (at the time) the most up-to-date version of mySQL and PHP there were some discrepancies with dates.

Then I realized something. For some reason when certain functions of the website posted things to the database that involved time, it was off.  When the database entries did not have hours,minutes,seconds and just the date, it’d always push the date to be the previous day. This was strange and very annoying. You would post a news entry, and it’d be displayed as if it were posted the day previous. If you posted a sports schedule, the game listing would be off a day (this could cause definite issues).

After investigating this further, it turned out that database entries with hours,minutes,seconds did display the time as 23 seconds behind. Always 23 seconds. Always. This makes sense, because on date fields that don’t have the hourly time written, it’s internally notated as midnight (00:00:00).  So, if you take 23 seconds away from midnight, it’s the previous day. What could cause this? PHP (the programming language that’s calling the data from the database) and mySQL is running off the same machine with the same clock, so it’s not like it’s desync’d.

After even further investigation, I traced down the issue. Apparently the international time committee decided to implement leap seconds in the UTC time standard. These leap seconds are implemented to compensate for the gradual slowing of the earth’s rotation due to the tidal effect. mySQL accounts for the leap seconds. PHP doesn’t. Or maybe vice versa. I don’t know. I would think if leap seconds are added, it’d be 23 seconds ahead. Perhaps I’m just thinking backwards about this.

Apparently these people don’t think of the consequences of their decisions in regards to hardware and software where time precision is required. Especially when midnight is used for the baseline in setting times. Right now I’m going to investigate the possibility of changing the mySQL timezone in favor of a “time standard” that doesn’t reflect leap seconds. Perhaps GMT-5.  instead of UTC-6. Or whatever.

Regardless, this just shows why UTC should not be used when consistency is required.