This evening I was trying to select entries within a specific date range, for example, all torrents for films which had been released in 2007. My query looked something like:
SELECT name FROM table WHERE date > 2007-01-01 AND date < 2008-01-01
PostgreSQL consistently returned very odd results. As far as it was concerned, Transformers was not a 2007 release, furthermore Batman Begins - which I distinctly remember going to see about three years ago - was.
Any relatively experienced SQL hacker is no doubt chuckling, having immediately seen my error. Of course, PostgreSQL is treating the un-quoted dates as arithmetic expressions and evaluating them numerically. When you think about it, 2005 - 05 - 05 = 1995. This is a perfectly valid arithmetic expression, its just that it happens to look like a definitive calendar date to my brain.
I found this mistake on my part absolutely hilarious.
Niall O'Higgins is an author, event organizer and software consultant. He wrote the book MongoDB and Python, published by O'Reilly. Events he organizes include We Have Tablets, the #1 Bay Area Tablet Computing Meet-up and PyWebSF. He also offers consulting services for Mobile, Tablet and Cloud Computing.
