Exercise 17: Dates, Times and ORDER BY

I'm going to tell you a little story about SQL and time. I'm not sure if this story is exactly true, but it's what I was told when I asked why dates are so totally weird in SQL. Back in the early days of computers the main thing that made money was big giant computers, and big giant databases. Mostly because the government and banks needed them. During this time there were huge wars between giant computer companies and to create peace among them the SQL standard was born. With the standard in place these companies could sell their databases to customers who didn't need to worry if their Database Administrators (DBA) would need to be retrained.

The problem is, if every database uses the exact same language then they become a commodity. IBM's database too expensive? With truly standardized SQL you can just buy an Oracle database and flip a switch. Software was (and still is) very much about locking people into a deadly embrace to extract long term profits for substandard goods. To prevent this commodity status, the SQL companies looked for holes in the standard that they could tweak so that their version of SQL was "compliant" with the standard, but also had something very specific to their database that prevented you from changing to a competitor.

The perfect match was found in dates and time. You see, calendar systems in software are a total disastrous mess already with no real logical standards. Rather than try to standardize time itself, the SQL standard simply left it open ended and terribly unspecified. Through this tiny doorway the database companies crammed every possible little differentiation they could to keep customers from jumping ship. Every database stores time different, formats it weirdly, and has their own flavor because of the accident of history that it kept banks from switching to Oracle from IBM.

Whether that story is true or not, it is true that dates in SQL are always weird and you always have to look them up for every database you touch. Thankfully most of the database access you'll do will be through abstraction layers that take your fancy Python or Ruby code and translate it into the flavor of SQL your database needs. But, when you're doing manual SQL work, you have to look it up. Don't make any assumptions about how the dates are stored, accessed, formatted, or even if they have a date column type.

What?! Yes, believe it or not SQLite3 does not have an actual date type. It just writes your date into a text type and lets you deal with the dates using a set of functions. The good news is that SQLite3 has really great date and time functions that handle many of the calendar calculations you could ever need. Read the Date and Time Functions documentation, and then try these sample queries: