CREATE DEFINER = CURRENT_USER FUNCTION `func_time_europe_to_beijing`(`europe_time` datetime)
RETURNS datetime
? ? COMMENT '歐洲中部時(shí)間（帶夏令時(shí)）轉(zhuǎn)換成北京時(shí)間'
BEGIN
DECLARE is_summer INT ;
DECLARE summer_start_date , summer_end_date DATETIME;
DECLARE day_of_week INT ;
-- 獲取當(dāng)年夏令時(shí)的開始和結(jié)束時(shí)間
-- 歐洲中部夏令時(shí)：每年3月最后一個(gè)周日凌晨2點(diǎn)開始，至10月最后一個(gè)周日凌晨2點(diǎn)結(jié)束
SET summer_start_date = STR_TO_DATE(concat(year(europe_time),'-03-31 02:00:00' ),'%Y-%m-%d %H:%i:%s'); -- 當(dāng)年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);-- 往前倒推至最近一個(gè)周日
END IF;
SET summer_end_date = STR_TO_DATE(concat(year(europe_time),'-10-31 02:00:00' ),'%Y-%m-%d %H:%i:%s'); -- 當(dāng)年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);-- 往前倒推至最近一個(gè)周日
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
-- 夏令時(shí)比UTC快兩個(gè)小時(shí)
RETURN convert_tz(europe_time, '+02:00', '+08:00');
ELSE
-- 冬令時(shí)比UTC快一個(gè)小時(shí)
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 '北京時(shí)間轉(zhuǎn)換成歐洲中部時(shí)間（帶夏令時(shí)）'
BEGIN
DECLARE is_summer INT ;
DECLARE summer_start_date , summer_end_date DATETIME;
DECLARE day_of_week INT ;
-- 獲取當(dāng)年夏令時(shí)的開始和結(jié)束時(shí)間
-- 歐洲中部夏令時(shí)：每年3月最后一個(gè)周日凌晨2點(diǎn)開始，至10月最后一個(gè)周日凌晨2點(diǎn)結(jié)束
SET summer_start_date = STR_TO_DATE(concat(year(beijing_time),'-03-31 02:00:00' ),'%Y-%m-%d %H:%i:%s'); -- 當(dāng)年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);-- 往前倒推至最近一個(gè)周日
END IF;
SET summer_end_date = STR_TO_DATE(concat(year(beijing_time),'-10-31 02:00:00' ),'%Y-%m-%d %H:%i:%s'); -- 當(dāng)年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);-- 往前倒推至最近一個(gè)周日
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
-- 夏令時(shí)比UTC快兩個(gè)小時(shí)
RETURN convert_tz(beijing_time, '+02:00', '+08:00');
ELSE
-- 冬令時(shí)比UTC快一個(gè)小時(shí)
RETURN convert_tz(beijing_time, '+01:00', '+08:00');
END IF;
END;;
