1

I am hosting a website on a shared server. They very helpfully (not) are refusing to set up the timezone tables. Not to be undone, I set up my own using the documentation (both ways) 1) via the SQL and 2 via the tables In both cases all the tables have data. In both cases select CONVERT_TZ ('2015-09-15 23:59:00', from_tz, to_tz) returns null.

I have also tried to set it up on my Windows (again both ways)

Again, it returns null. If I use select CONVERT_TZ ('2015-09-15 23:59:00', '+2:00','-1:00') it works.

Any ideas? Alternatively, can someone provide the code for CONVERT_TZ?

Matt Johnson-Pint
  • 230,703
  • 74
  • 448
  • 575
MaxSunshine
  • 11
  • 3
  • Just for verification, please provide example strings form `from_tz` and `to_tz` that cause `CONVERT_TZ` to return null. – Matt Johnson-Pint Sep 30 '15 at 19:34
  • Also, when you said you did it using the documentation, you mean as described [here](https://dev.mysql.com/doc/refman/5.7/en/time-zone-support.html#time-zone-installation)? – Matt Johnson-Pint Sep 30 '15 at 19:37
  • Thank you for the reply. Convert_TZ relies on the mysql database. Not a local version. On a shared server with unhelpful support, you have to DIY it. I created the tables and the data and then created the following function: – MaxSunshine Oct 01 '15 at 11:29

1 Answers1

0
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 ;
MaxSunshine
  • 11
  • 3