Sorting dates stored as varchar/rfc822

Question

Don't ask why (it's something out of my control), but dates are being stored as RFC-822 in our MySQL DB in varchar(125).

RFC-822 = Mon Jun 13 2011 11:30:00 GMT-0400 (EDT) or Mon Jun 13 17:00:00 EDT 2011

Is there a way I can sort by date in that format or at the very least, pull the date out as YYYYMMDD or Unix time?

Answer 1

Some voodoo can help with the first format:

SET @dt = 'Mon Jun 13 2011 11:30:00 GMT-0400 (EDT)';

SELECT    
CONVERT_TZ(
  -- Parse all, but timezone
  STR_TO_DATE(@dt, '%a %b %e %Y %H:%i:%s'),

  -- Parse timezone to '+NN:NN' format
  INSERT(SUBSTRING_INDEX(SUBSTRING_INDEX(@dt, 'GMT', -1), ' ', 1), 4, 0, ':'),

  -- Our unified timezone
  '+00:00'
);

-- Result: 2011-06-13 15:30:00

CONVERT_TZ supports EDT-like abbreviations too, but not everywhere.