Preparing a Prepare statement in mysql and using convert_tz in it

Question

I have written a prepare statement like this and is working fine.

BEGIN
SET @tempaccountID :=CONCAT('\'',accountID1,'\'');
SET @tempdeviceID := CONCAT('\'',deviceID1,'\'');
SET @query :=CONCAT('select accountID, 
            deviceID, 
            FROM_UNIXTIME(timestamp) as timestamp, 
            statusCode, 
            latitude, 
            longitude, 
            speedKPH, 
            heading, 
            address, 
            odometerKM,
            Charging
            from ',(SELECT eventtableName FROM  migration_run_time WHERE accountID=accountId1 AND deviceID=deviceID1),
            ' where accountID=',@tempaccountID,
            ' and deviceID=',@tempdeviceID,
            ' and latitude!=0.0 and longitude!=0.0  and speedKPH<120 and timestamp= (select max(timestamp) from ', (SELECT eventtableName FROM  migration_run_time WHERE accountID=accountId1 AND deviceID=deviceID1) ,
                                                ' where accountID=',@tempaccountID,
                                                ' and deviceID=',@tempdeviceID,
                                                ' and latitude!=0.0  and longitude!=0.0 and speedKPH<120);');


PREPARE stmt FROM @query;
EXECUTE stmt;

Now coming to big problem

I want to use Convert_tz in the statement to get the time in required time format like this

convert_tz(FROM_UNIXTIME(timestamp),device.timeZone,device.requiredTimeZone) as timestamp

// device is another table in the above statement

if I will write this in above manner, I'll have to prefix the table names in fromt of coloumn names but the event-table itself is calculated on runtime on the basis of accountID and deviceID..

how should I go about IT...

P.S. I am pretty bad at mysql.. I am a >net/jQuery developer and have no IDea about mysql. please help.. :(

Answer 1

Finally found the solution... I created two variables for this @timeZone and @requiredTimeZone..

thank you @TOMBOM for the help...

DELIMITER $$

USE `hiddenFromSO`$$

DROP PROCEDURE IF EXISTS `getLastCoordinate`$$

CREATE DEFINER=`root`@`%` PROCEDURE `getLastCoordinate`(accountID1 VARCHAR(32),
                        deviceID1 VARCHAR(64))
BEGIN
    SET @tempaccountID :=CONCAT('\'',accountID1,'\'');
    SET @tempdeviceID := CONCAT('\'',deviceID1,'\'');

    SET @timeZone=CONCAT('\'',(SELECT timeZOne FROM device WHERE deviceID=deviceID1 AND accountID=accountID1 AND isActive='1'),'\'');

    SET @requiredTimeZone=CONCAT('\'',(SELECT requiredTimeZone FROM device WHERE deviceID=deviceID1 AND accountID=accountID1 AND isActive='1'),'\'');

    SET @query :=CONCAT('select accountID, 
                deviceID, 
                convert_tz(FROM_UNIXTIME(timestamp),',@timeZone,',',@requiredTimeZone,') as timestamp, 
                statusCode, 
                latitude, 
                longitude, 
                speedKPH, 
                heading, 
                address, 
                odometerKM,
                Charging
                from ',(SELECT eventtableName FROM  migration_run_time WHERE accountID=accountId1 AND deviceID=deviceID1),
                ' where accountID=',@tempaccountID,
                ' and deviceID=',@tempdeviceID,
                ' and latitude!=0.0 and longitude!=0.0  and speedKPH<120 and timestamp= (select max(timestamp) from ', (SELECT eventtableName FROM  migration_run_time WHERE accountID=accountId1 AND deviceID=deviceID1) ,
                                                    ' where accountID=',@tempaccountID,
                                                    ' and deviceID=',@tempdeviceID,
                                                    ' and latitude!=0.0  and longitude!=0.0 and speedKPH<120);');


     PREPARE stmt FROM @query;
    -- select @query;
    EXECUTE stmt;
    END$$

DELIMITER ;