Nerd times

Gotta brag.

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.

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: Mon Aug 27 00:43:21 2007 . If you simply sort on it, you end up sorting by day of week. “Fri” comes before “Thu”. Not very useful.

Fortunately, mysql has a function for exactly this situation: STR_TO_DATE. Here’s the new code:

my $sth = $dbh->prepare( qq{ SELECT STR_TO_DATE(stime, '%a %b %e %H:%i:%s %Y') as the_date, jobnumber,
                                        exit_status, wallclk, cpu, jobs, jobname 
                                 FROM dms_jobs
                                 WHERE
                                 username = ? order by the_date desc
                               } ) ;

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.