CREATE DEFINER = CURRENT_USER FUNCTION `func_time_europe_to_beijing`(`europe_time` datetime)
RETURNS datetime
COMMENT '欧洲中部时间（带夏令时）转换成北京时间'
BEGIN
DECLARE is_summer INT ;
DECLARE summer_start_date , summer_end_date DATETIME;
DECLARE day_of_week INT ;
-- 获取当年夏令时的开始和结束时间
-- 欧洲中部夏令时：每年3月最后一个周日凌晨2点开始，至10月最后一个周日凌晨2点结束
SET summer_start_date = STR_TO_DATE(concat(year(europe_time),'-03-31 02:00:00' ),'%Y-%m-%d %H:%i:%s'); -- 当年3月最后一天
SET day_of_week = DAYOFWEEK(summer_start_date);
IF day_of_week > 1 THEN
SET summer_start_date = ADDDATE(summer_start_date,INTERVAL 1-day_of_week DAY);-- 往前倒推至最近一个周日
END IF;
SET summer_end_date = STR_TO_DATE(concat(year(europe_time),'-10-31 02:00:00' ),'%Y-%m-%d %H:%i:%s'); -- 当年10月最后一天
SET day_of_week = DAYOFWEEK(summer_end_date);
IF day_of_week > 1 THEN
SET summer_end_date = ADDDATE(summer_end_date,INTERVAL 1-day_of_week DAY);-- 往前倒推至最近一个周日
END IF;
IF europe_time > summer_start_date AND
europe_time < summer_end_date THEN
SET is_summer = 1;
END IF;
IF is_summer = 1 THEN
-- 夏令时比UTC快两个小时
RETURN convert_tz(europe_time, '+02:00', '+08:00');
ELSE
-- 冬令时比UTC快一个小时
RETURN convert_tz(europe_time, '+01:00', '+08:00');
END IF;
END;
CREATE DEFINER = CURRENT_USER FUNCTION `func_time_beijing_to_europe`(`beijing_time` datetime)
RETURNS datetime
COMMENT '北京时间转换成欧洲中部时间（带夏令时）'
BEGIN
DECLARE is_summer INT ;
DECLARE summer_start_date , summer_end_date DATETIME;
DECLARE day_of_week INT ;
-- 获取当年夏令时的开始和结束时间
-- 欧洲中部夏令时：每年3月最后一个周日凌晨2点开始，至10月最后一个周日凌晨2点结束
SET summer_start_date = STR_TO_DATE(concat(year(beijing_time),'-03-31 02:00:00' ),'%Y-%m-%d %H:%i:%s'); -- 当年3月最后一天
SET day_of_week = DAYOFWEEK(summer_start_date);
IF day_of_week > 1 THEN
SET summer_start_date = ADDDATE(summer_start_date,INTERVAL 1-day_of_week DAY);-- 往前倒推至最近一个周日
END IF;
SET summer_end_date = STR_TO_DATE(concat(year(beijing_time),'-10-31 02:00:00' ),'%Y-%m-%d %H:%i:%s'); -- 当年10月最后一天
SET day_of_week = DAYOFWEEK(summer_end_date);
IF day_of_week > 1 THEN
SET summer_end_date = ADDDATE(summer_end_date,INTERVAL 1-day_of_week DAY);-- 往前倒推至最近一个周日
END IF;
IF beijing_time > summer_start_date AND
beijing_time < summer_end_date THEN
SET is_summer = 1;
END IF;
IF is_summer = 1 THEN
-- 夏令时比UTC快两个小时
RETURN convert_tz(beijing_time, '+02:00', '+08:00');
ELSE
-- 冬令时比UTC快一个小时
RETURN convert_tz(beijing_time, '+01:00', '+08:00');
END IF;
END;;
网友评论