3

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?

bafromca
  • 1,926
  • 4
  • 27
  • 42
  • Is there any way to run this through PHP or some other scripting language? As far as I can see, this is impossible in mySQL alone because [`STR_TO_TIME`](http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_str-to-date) can't handle the time zone bit – Pekka Jun 13 '11 at 17:15

1 Answers1

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.

Pavel Koryagin
  • 1,479
  • 1
  • 13
  • 27
  • That's quite the query and quite the help. Thank you! Now I'll take your example and modify it for `Mon Jun 13 17:00:00 EDT 2011`. Thank you once again. – bafromca Jun 14 '11 at 14:09
  • I ended up using `SET @dt = 'Fri Jul 1 13:00:00 EDT 2011';` and `SELECT CONCAT(SUBSTRING_INDEX(@dt, 'EDT', -1),'-', SUBSTRING_INDEX(STR_TO_DATE(@dt, '%a %b %e %H:%i:%s'),'0000-', -1)) AS datetime;` Thanks! – bafromca Jun 21 '11 at 14:43