对存储为 varchar/rfc822 的日期进行排序

Question

不要问为什么（这是我无法控制的），但日期在我们的 MySQL 数据库中以 RFC-822 的形式存储在 varchar(125) 中。

RFC-822 = 2011 年 6 月 13 日星期一 11:30:00 GMT-0400 (EDT) 或 2011 年 6 月 13 日星期一 17:00:00 EDT

有什么办法可以按该格式的日期排序，或者至少将日期提取为 YYYYMMDD 或 Unix 时间？

Answer 1

一些 voodoo 可以帮助处理第一种格式：

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 也支持类似 EDT 的缩写，但不是所有地方都支持。