使用Mysql将UTC 12小时时间格式转换为印度时间

我在Mysql中编写了一个事件调度程序，用于在用户旅行时间之前3小时生成票证。Mysl安装在服务器时间为UTC和12小时格式的服务器上，与IST时间相差5:30。我有一个用户的旅行时间是早上7:30，所以我需要在早上4:30为该用户生成票证。我正在获取当前服务器时间并转换为IST，并比较旅行时间和当前转换后的时差为3个小时。但是有些问题是它失败的原因，并且总是在早上10点创建票证。以下是我用Mysql编写的完整事件，

DELIMITER $$

ALTER EVENT `Auto_Generated_Ticket` ON SCHEDULE EVERY 1 MINUTE STARTS CURRENT_TIMESTAMP ON COMPLETION PRESERVE ENABLE DO BEGIN
     DECLARE UserId INT;
     DECLARE v_finished INT DEFAULT 0;
     DECLARE GetDate DATE DEFAULT DATE(NOW());


     /*get all active user's who's tariff enddate is > today and available journeys > 0 and pass-status=4(delivered)*/
     DECLARE ActiveUserId CURSOR FOR 
       SELECT UT.user_id FROM `um.user_trs.tariff` UT
    INNER JOIN `um.user` U ON U.user_id=UT.user_id
    INNER JOIN `um.user_ps.pass` UP ON UP.user_id=UT.user_id
    INNER JOIN `ps.pass` P ON P.pass_id=UP.pass_id
    WHERE UT.end_date >=DATE(NOW()) AND UT.available_journeys > 0 AND UT.current_balance>0 AND P.status_id=4
    GROUP BY UT.user_id;

     DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_finished=1;
     SET @GetTime= DATE_FORMAT(NOW(),'%Y-%m-%d %h:%i:%s %p');
     OPEN ActiveUserId;
       get_userid:LOOP
       FETCH ActiveUserId INTO UserId;
       IF v_finished=1 THEN
        LEAVE get_userid;
        END IF;

         SET @StartTime=(SELECT RS.start_time FROM `um.user_rm.route` UR 
                INNER JOIN `rm.route_schedule` RS ON RS.route_schedule_id=UR.route_schedule_id
                WHERE UR.user_id=UserId
                ORDER BY ABS( TIMEDIFF( RS.`start_time`, TIME(CONVERT_TZ(@GetTime,'+00:00','+05:30')) ) ) LIMIT 1);

         SET @TimeDiff=(HOUR(TIMEDIFF(@StartTime,TIME(CONVERT_TZ(@GetTime,'+00:00','+05:30')))));


          /*if time difference between current time and schedule start time is 3 hours then generate ticket for the user for that particular schedule*/
         IF (@TimeDiff =3) THEN
            /*IF (@TNumber IS NULL) THEN*/
            IF NOT EXISTS(SELECT ticket_number FROM `um.user_ts.ticket` WHERE route_id=@RouteId AND route_schedule_id=@RoutScheduleId
                   AND user_id=UserId AND DATE(date_of_issue)=@ISTDATE) THEN
             INSERT INTO `um.user_ts.ticket` 
                 (
                  `user_id`,`route_id`,`route_schedule_id`,`ticket_number`,`date_of_issue`,`is_cancelled`,
                  `amount_charged`,`user_tariff_id`,`opening_balance`,`is_auto_generated`
                 )
                VALUES
                (
                 UserId,@RouteId,@RoutScheduleId,@TicketNumber,CONVERT_TZ(UTC_TIMESTAMP(),'+00:00','+05:30'), 
                 FALSE,@PerJourneyCost,@UserTariffId,@TariffCurrentBalance,1
                );


           END IF;/*end of route and schedule check*/
         END IF; /*end of time difference check*/   
       END LOOP get_userid;
       CLOSE ActiveUserId;      
    END$$

DELIMITER ;

请让我知道是否有其他方法可以转换时间或我在上述查询中没有注意到的任何错误。

关于Sangeetha