Gotta brag.<\/p>\n
We’ve got an application that uses MySQL to store lists of things. There was a case today where we suddenly wanted to sort by a particular date that was in a field in the table. <\/p>\n
Unfortunately, we don’t use a “date” field in mysql for this information. We use a string of 30 characters. Yes, yes. Kill me. Trust me, if I got to fix everything in our code that sucks, that would be one of the very last changes. We then happen to stick characters into that string that a human might read as a date, like this: Fortunately, mysql has a function for exactly this situation: STR_TO_DATE. Here’s the new code:<\/p>\n I’m really glad that I looked in the mysql docs to see if they had a solution before embarking on some sort of regular expression odyssey in perl.<\/p>\n","protected":false},"excerpt":{"rendered":" Gotta brag.<\/p>\nMon Aug 27 00:43:21 2007 <\/code>. If you simply sort on it, you end up sorting by day of week. “Fri” comes before “Thu”. Not very useful.<\/p>\nmy $sth = $dbh->prepare( qq{ SELECT STR_TO_DATE(stime, '%a %b %e %H:%i:%s %Y') as the_date, jobnumber,\r\n exit_status, wallclk, cpu, jobs, jobname \r\n FROM dms_jobs\r\n WHERE\r\n username = ? order by the_date desc\r\n } ) ;<\/pre>\n