I’m walking 62 miles in less than 30 hours!

I’ve formed a team to take on the Oxfam Trailwalker Challenge – a 62 mile walk on the South Downs that must be undertaken in less than 30 hours! Our training is well underway, and it looks like we’ll be shooting for around 25 hours.

On the big day I’ll be sending pics and reports to the team blog, and you’ll also be able to send us messages of support via SMS too.

We’re hoping to raise £1500 for Oxfam, so if you can possibly afford it, please consider sponsoring me.

You can keep track of our training and out how to sponsor us on our team blog at http://trailwalker.dixo.net

MySQL gotcha using BETWEEN with dates

We just updated a development server from Ubuntu 6.06 to 6.10. As a result, MySQL went up a couple of point releases from 5.0.22 to 5.0.24a.

Suddenly, the web application we were working on stopped working. It wasn’t possible to log in.

We quickly narrowed it down to a clause in query which checked a users subscription was valid by checking its start and end dates. It looked bit like this:

SELECT foo FROM bar WHERE NOW() BETWEEN start AND end;

MySQL 5.0.22 returned rows, but 5.0.24 didn’t. After scratching our heads, I checked the manual:

For best results when using BETWEEN with date or time values, you should use CAST() to explicitly convert the values to the desired data type. Examples: If you compare a DATETIME to two DATE values, convert the DATE values to DATETIME values. If you use a string constant such as ‘2001-1-1’ in a comparison to a DATE, cast the string to a DATE.

The phrase “best results” make it sound like we’re baking a cake, and that your query results will be fluffier and more flavoursome if you use CAST! What they actually mean is, if you’re comparing different date types, you’re screwed if you don’t cast. You might get nothing.

Sure enough, our start and end columns were dates, and using cast in the query worked just fine:

SELECT foo FROM bar WHERE CAST(NOW() AS DATE)  BETWEEN start AND end;

I couldn’t find anything directly related to this change in behaviour in the changelogs, but hopefully this will help someone out there…