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 and software developer. He wrote the O'Reilly book MongoDB and Python. He is the co-founder of BeyondFog, Inc which makes Strider Brilliant Continuous Deployment. Strider is a hosted Continuous Integration & Deployment service for Node.JS and Python.
