Niall’s Weblog

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.

This entry was posted on Monday, March 17th, 2008 at 9:14 pm and is filed under Database, Technical. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.