Epic Epoch Fail

I shit you not:

In the SQLite database in which Apple stores your iPhone text messages, each record has, among other things, a numeric field called 'date'.

If the message in question is an SMS, then that's what you'd expect: the number of seconds since the Unix Epoch, 1-Jan-1970 GMT. But if the message is an MMS or an iMessage, then it's the number of seconds since 1-Jan-2001 GMT. Same field. Same database. All interleaved.

$h->{date} += 978307200 if ($h->{is_madrid});

There is no emoticon for the feeling that I am having right now.

Tags: , , , , ,

28 Responses:

  1. Jon Parise says:

    For reference (ha!), Apple calls that a "reference date". See also: Epoch (reference data).

    This comment in no way condones mixing (arbitrary) reference dates with dates based on the Unix Epoch.

  2. Not even (ノಠ益ಠ)ノ彡┻━┻?

  3. Oh, so you can tell from the high bit.

    • jwz says:

      Not really. That's the same as saying, "you can tell by whether it's < 5-Jan-1987".

      • Josh Adams says:

        I was under the assumption 'the high bit' was suggesting the state the engineers that came up with that were in. They'd set the 'high' bit...

        • Leonardo Herrera says:

          I found this subthread amusing in an impedance-mismatch sense.

          • Jeffrey Paul says:

            Thanks to the logical predecessor to this webpage, I can no longer hear the phrase "impedance mismatch" in any non-electrical context without also hearing my mental voice pronounce the capitals in the phrase "Extra Special Annoying".

    • Except for SMS sent post 2001 (real human-being dates). Unless I've missed something, and I did go look at my local version of SQLite "database" in question first, so I'm pretty sure I'm not.

  4. Ian McKellar says:

    SQLite is the new mork.

    • Jesper says:

      I thought the point with mork was that it was horrible through and through as a database format. This is just questionable data modeling. If you make this same mistake in a Redis store, is Redis the new mork?

    • Bullshit. SQLite is exactly what it advertises itself to be: it accepts SQL queries, but it's not actually an RDBMS.

      That's a thing that has purposes, although I don't agree with all the purposes to which Apple has put it.

  5. KevinW says:

    Is there a term for this species of buzzkill? When a fileformat/protocol/database/API is so close to being clean and orthogonal, but then there's this one gotcha that turns it into a special-case-ridden mess?

    It seems to happen quite a bit.

  6. Reminds me of Excel which avoided 1900 in favor of 1904 for one epoch to avoid a tricky(?) (non) leap year. But, um, only on Macs.

    • Jussi says:

      Sounds like Excel was using the native date of classic Mac, which started epoch from 1904. Reason seems to be the one you say. Sounds a bit silly, but I guess it was worth it in the 80s.

  7. 205guy says:

    I realize you're trying to do something neat and maybe even useful, and you don't need my snarky comment, but trying to distinguish between three different forms of messages that should essentially be the same was your first mistake. JFC, is this the 21st century or what? Why can't I just send a text-based interrupt-driven private message to Mr. X wherever he is currently active?

    For you people out there looking to design the next twitter, make a matrix of the following dimensions: private/public, text/voice/video, addressable/random, interrupt/push/poll, failover mode (which point to another cell of your crosstab), and probably some other dimensions you can figure out. I convinced this is how they came up with chat roulette.

    A few are already implemented, such as walkie-talkie, email, twitter, but could probably be re-invented on each network, web n.0. Though that would lead to the the dreaded fragmentation that I curse, so please work on that universal addressability first, but in a way that Google can't track me (in other words, why do I need a phone number and an email address?)

  8. Adam says:

    I got it: They have fixed the 2038 problem by making it the 2069 problem instead. Yay!

  9. Monkey Mind says:

    Well, one possible facepalm emoticon is m(

  10. Igor Elland says:

    Hmmm what about "nice job, Apple"? Save a few bytes per date per sms/iMessage might not feel like much for a web developer, but for embedded systems, it can amount to quite a number. Like saving 10 kbytes by message when you have 500 messages' still saves you ~5MB. :) That's a whole app or 1-2 songs right there, fitting on your iPod. So thank Apple.

  11. Jeffrey Paul says:

    Yes, braindamaged - but is it really that big of a deal? I'm sure whatever fruity internal accessor api is in use does the exact same thing as your perl one-liner... and that's the only way that anyone in the design criteria would ever be accessing the database anyway. Write once in the abstraction model, never worry about again. Anyone competent enough to jailbreak and scp the records off would surely be competent enough to notice and write one line of perl to correct - which you were.

    What's the sin here? It also may be noted that iOS generally ships on time, and is making them still-uncounted billions-with-a-b.

    (I'm resisting the urge to make a "duct tape" comment here. Actually, I guess I'm not.)

    • jwz says:

      Your question makes me hope you are not employed as a programmer.

      It's shitty non-design resulting in shitty and fragile engineering. If you worked for me and pulled some crap like this I'd fire your ass to reduce the chance that I might someday need to try and debug or maintain your code.

      Beyond that, whoever thought it was a good idea to pick a new epoch in the first place is a Mork level of insane.

      But hey, they make a lot of money so that must make non-decisions like this be the correct non-decisions. And the Legions of Mammon Tremble!

      • Leonardo Herrera says:

        I work for a big enterprise in the... let's say, financial sector. You would most likely get a stroke if you ever worked here.

        This non-comment of the day is just to let you know... hush, there, there.