sql 时区 夏令时 冬令时

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