MySQL ISO Date日期格式转换

原始数据：2017-1-1T11:58:00+09:00(ISO DATE 带时区）
需要转成：2017-1-1T20:58:00Z(UTC+0)
然后再转：2017-1-2 04:58:00(中国UTC+8）

MYSQL下

select from_unixtime(ISO8601TOUNIXTIMESTAMP('2017-12-01T15:54:27+01:00'))

-> 2017-12-01 14:54:27.000000

select DATE_FORMAT(from_unixtime('1512111267'),'%Y-%m-%dT%H:%i:%sZ')
-> 2017-12-01T14:54:27Z

select date_add(DATE_FORMAT(from_unixtime('1512111267'),'%Y-%m-%dT%H:%i:%sZ'), interval 8 hour); /*中国是UTC+8*/
-> 2017-12-01 22:54:27

DELIMITER $$

DROP FUNCTION IF EXISTS `amazon`.`ISO8601TOUNIXTIMESTAMP`$$

CREATE DEFINER=`root`@`%` FUNCTION `ISO8601TOUNIXTIMESTAMP`(iso varchar(25)) RETURNS int(15)
    DETERMINISTIC
BEGIN
DECLARE CONVTIME INTEGER(11);
SET CONVTIME = (SUBSTRING(iso,21,2) * 60) + SUBSTRING(iso,24,2);
IF SUBSTRING(iso,20,1) = '+' THEN 
SET CONVTIME = 0 - CONVTIME;
END IF; 
RETURN UNIX_TIMESTAMP(DATE_ADD(STR_TO_DATE(CONCAT(SUBSTRING(iso,1,10),' ',SUBSTRING(iso,12,8)),'%Y-%m-%d %H:%i:%s'), INTERVAL CONVTIME MINUTE));
    END$$

DELIMITER ;

见“https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_date-format”