semi/signal

I was curious what to name a variable holding a string with a date formatted for MySQL. Something like “startDateMysql” seemed silly, so I went digging as to what MySQL’s date format was based on.

The YYYY-MM-DD format used by MySQL is defined by ISO 8601.

The HH:MM:SS format is also defined by ISO 8601, but MySQL never adds the time zone designator, a ‘Z’ indicator for UTC or an offset from UTC. Without the time zone designator, the time is implied to be local time according to the standard, but MySQL returns non-local time without a designator, which is non-conformant.

When combining a date and time, MySQL omits the ‘T’ delimiter, which is allowed and permitted by “mutual agreement.”

Seems like I’m talking too much about firesync in this blog, but anyway, another rare, but serious bug popped up in firesync on this special day. However, unlike the previous bug (see previous blog entry), this one doesn’t seem to be unique to firesync; it’s the daylight savings time bug (note: this article is informative, but some parts are not written too clearly and can be difficult to understand).

The bug popped up when I noticed firesync seemed to be thinking that all of my files were out-of-sync. I looked at the file modification times of a few files on the two PCs and noticed that modification, creation, and access times were off by 1 hour. The reason for this, as I discovered, is that the filesystem of one system was FAT32 while the other was NTFS. FAT32 time stamps are equal to the local time (so, inherently, DST information is contained within the time stamp). NTFS time stamps are based on UTC time and the local time is calculated as an offset from the UTC time. The problem (i.e. the daylight savings bug) results from the fact that Microsoft chose a somewhat odd way of handling conversion from UTC-to-local time. Instead of storing DST information as part of a time stamp (which would enable a correct conversion), NTFS systems will add 1 hour to the UTC time (during the UTC-to-local time conversion process) if daylight savings is currently in effect!

So, the solution seems to be to do all time stamp comparisons based on UTC time. Ahh, more work for firesync 2.