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;;
