Geek asks and answers

Date sort is stored as varchar / rfc822

Do not ask why (this is something out of my control), but the dates are stored in RFC-822 in our MySQL DB in varchar (125).

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

Is there a way to sort by date in this format, or at least display the date as YYYYMMDD or Unix?

+3
source share
1 answer

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 also supports abbreviations EDT, but not everywhere.

+1
source

More articles:


All Articles