使用 time_zone_name 时 MySQL CONVERT_TZ 返回 null

Question

我在共享服务器上托管一个网站。他们非常有帮助(没有)拒绝设置时区表。为了不被撤销，我使用文档(两种方式)1)通过 SQL 和 2 通过表设置了自己的在这两种情况下，所有表都有数据。在这两种情况下select CONVERT_TZ('2015-09-15 23:59:00', from_tz, to_tz) 返回 null。

我也尝试在 Windows 上进行设置(同样是两种方式)

它再次返回 null。如果我使用选择 CONVERT_TZ ('2015-09-15 23:59:00', '+2:00','-1:00') 它有效。

有什么想法吗？或者，有人可以提供 CONVERT_TZ 的代码吗？

Answer 1

DELIMITER $$
CREATE DEFINER=`root`@`localhost` FUNCTION `convert_tz_shared`(
local_date_time timestamp,
local_tz varchar(50),
dest_tz varchar(50) 
) RETURNS datetime
READS SQL DATA
DETERMINISTIC
BEGIN
     DECLARE secs  int;
     DECLARE local_tz_secs int;
     DECLARE dest_tz_secs int;
     DECLARE tmp_tz varchar(50);
     # convert if possible to seconds 
     IF (left(local_tz,1)='+' OR left(local_tz,1)='-') 
     THEN
          SET @tmp_tz:=SUBSTRING(local_tz,2,LENGTH(local_tz));
          SET @local_tz_secs:=SUBSTRING_INDEX(@tmp_tz,':',1)*3600 + SUBSTRING_INDEX(@tmp_tz,':',-1)*60;
          IF (left(local_tz,1)='-') THEN
             SET @local_tz_secs:= -@local_tz_secs;
          END IF;
     END IF ;

     IF (left(dest_tz,1)='+' OR left(dest_tz,1)='-')
     THEN 
          SET @tmp_tz:=SUBSTRING(dest_tz,2,LENGTH(dest_tz));
          SET @dest_tz_secs:=SUBSTRING_INDEX(@tmp_tz,':',1)*3600 + SUBSTRING_INDEX(@tmp_tz,':',-1)*60;
          IF (left(dest_tz,1)='-') THEN
             SET @dest_tz_secs:= -@dest_tz_secs;
          END IF;
     END IF;

     IF (left(dest_tz,1)<>'+' AND left(dest_tz,1)<>'-')
     THEN
          SET @dest_tz_secs:=
            (SELECT 
         # distinct n.name, n.time_zone_id, z.Use_leap_seconds,
          tt.offset
         #, tt.Is_DST, tt.Abbreviation,
         # FROM_UNIXTIME(t.Transition_time) AS p, 
         # t.Transition_type_id
         FROM time_zone_name AS n
         JOIN time_zone AS z USING(time_zone_id)
         JOIN time_zone_transition AS t USING (time_zone_id)
         JOIN time_zone_transition_type AS tt
          USING(time_zone_id,transition_type_id)
        WHERE NAME=dest_tz
         and FROM_UNIXTIME(t.Transition_time)<=local_date_time
         order by FROM_UNIXTIME(t.Transition_time) desc
        LIMIT 1);
    END IF;

    IF (left(local_tz,1)<>'+' AND left(local_tz,1)<>'-')
     THEN
      SET @local_tz_secs:=
      (SELECT # distinct n.name, n.time_zone_id, z.Use_leap_seconds,
      tt.offset
      #, tt.Is_DST, tt.Abbreviation,
      # FROM_UNIXTIME(t.Transition_time) AS p, 
      # t.Transition_type_id
      FROM time_zone_name AS n
      JOIN time_zone AS z USING(time_zone_id)
      JOIN time_zone_transition AS t USING (time_zone_id)
      JOIN time_zone_transition_type AS tt
      USING(time_zone_id,transition_type_id)
      WHERE NAME=local_tz
      and FROM_UNIXTIME(t.Transition_time)<=local_date_time
      order by FROM_UNIXTIME(t.Transition_time) desc
      LIMIT 1);
      END IF;

      RETURN DATE_ADD(local_date_time, INTERVAL (@dest_tz_secs - @local_tz_secs) SECOND);
      END$$
      DELIMITER ;