Ok, let me see if I can make this understandable in human language.

I have a table that gets written into in batches of 30 records every 2 1/2 hours by cellular modem. There is currently a procedure that parses these batch records into individual records. The problem is the time and date on the records is wrong. However, I am capturing the time at which the last record comes in. The readings are taken every five minutes.

What I'd like to know how to do is to take the time/date of the records and change their time based on the last record. Logically, this is how I envision this.

Batch gets written at 11-04-2009 13:47:52

Change time/date stamp on last record to match record written date.

For each previous record, subtract 5 minutes from last record to correct the time/date stamp.

I've never written procedures for MySQL so I kinda feel like the village idiot here. Thanks :)

Mmm tricky. Why not just create an extra DateTime field to store the time stamp and enter the data manually, rather than have an auto generated field like your existing one ?

Manual entry would be fine for one device but very soon we will be importing data from many of these systems. Every 2.5 hours, there will be another batch of records coming in. Add another 10 devices and now we are talking about manually updating 330 records by hand. That is my main concern.

You could do the following I would suggest writting a procedure and invoking after the load invoke it

Following do something like this

-- Be sure to declare @pmyDate  DATETIME;

select date_add(Max(columnDateTime), INTERVAL  -5 MINUTE) into @pmyDate  from yourtable

update table set columm=@pmyDate

Where  record is from the new batch.  -- this is your own where condition

Thanks, I'll try adding that to the existing stored procedure that does the raw record parsing.

I need some help bringing this new stuff into my existing SPs.  I'm going to list my stored procedures here. Please let me know where I should add the new SP...

parse_raw procedure (calls get_node_content):

DROP PROCEDURE IF EXISTS `parse_raw`;
DELIMITER $$

CREATE PROCEDURE `parse_raw`()
BEGIN

DECLARE x_id BIGINT;
DECLARE xtext text;
DECLARE useless1 text;
DECLARE useless2 text;
DECLARE x_device_string varchar(30);
DECLARE x_device_id BIGINT;
DECLARE slice_text text;
DECLARE time_text text;
DECLARE new_slice_id BIGINT;
DECLARE msmnt_tag VARCHAR(30);
DECLARE value_text text;
DECLARE x_msmnt_type_id BIGINT;

#Loop for all rows in table

main_loop: LOOP

#Get a row, if we can't we're done

SET x_id = -1;
SELECT ewise_raw_data_in.raw_id, ewise_raw_data_in.raw_data_xml FROM ewise_raw_data_in LIMIT 1 INTO x_id, xtext;
IF (x_id = -1) THEN
LEAVE main_loop;
END IF;

CALL get_node_content(xtext, 'energywise', xtext, useless1, useless2);
CALL get_node_content(xtext, 'device', x_device_string, useless1, xtext);

set x_device_id = 0;

SELECT ewise_device.device_id FROM ewise_device WHERE ewise_device.device_string = x_device_string INTO x_device_id;

IF (x_device_id = 0) THEN

INSERT INTO ewise_device VALUES (default, NULL, x_device_string, NULL, NULL, NULL, NULL);

SELECT LAST_INSERT_ID() INTO x_device_id;

END IF;

WHILE length(xtext) > 0 DO

CALL get_node_content(xtext, 'slice', slice_text, useless1, xtext);

IF length(slice_text) >0 THEN

CALL get_node_content(slice_text, 'time', time_text, useless1, slice_text);

INSERT INTO ewise_slice VALUES (default, x_device_id, time_text);

SELECT LAST_INSERT_ID() INTO new_slice_id;

WHILE length(slice_text) > 0 DO

set x_msmnt_type_id = 0;

CALL get_first_node_name(slice_text, msmnt_tag);

CALL get_node_content(slice_text, msmnt_tag, value_text, useless1, slice_text);

IF length(msmnt_tag) >0 THEN

SELECT ewise_msmnt_type.msmnt_type_id FROM ewise_msmnt_type WHERE ewise_msmnt_type.msmnt_type_code=LEFT(msmnt_tag,8) INTO x_msmnt_type_id;

IF (x_msmnt_type_id = 0) THEN

INSERT INTO ewise_msmnt_type VALUES (default, LEFT(msmnt_tag, 8), NULL, NULL);

SELECT LAST_INSERT_ID() INTO x_msmnt_type_id;

END IF;

INSERT INTO ewise_slice_msmnt VALUES (default, new_slice_id, x_msmnt_type_id, value_text); 

END IF;

END WHILE;

END IF;

END WHILE;

#Delete the data row
DELETE FROM ewise_raw_data_in WHERE ewise_raw_data_in.raw_id=x_id;

END LOOP main_loop;
END

$$

Just as a refresher, the raw_data_in table has the following columns: raw_id, raw_data_xml, raw_data_datetime

Thanks again for all the assistance :) 

No one has any idea where to put the new SP code? My thought was to take the timedate stamp that I place on the record batch, change the last record and then from there reduce the timestamp by 5 minutes per parsed XML record.

I just looked at your stored procedure, looks like a lot of work to adjust a datetime column back  5 minutes.  Let's start over,  perhaps a trigger might best serve you.

It would be automatically fired Before or After  the following Insert/Update/Delete you set it up.

Here is an example of a trigger that stores the database date in a column before the insert is done.  You could set the SomeDate column to the current time everytime a record is inserted. If you wanted to do an offset -5 minutes you would write the code to adjust the date as needed.

CREATE TRIGGER `test`.`tr_bi_Sometable` BEFORE INSERT
    ON test.sometable FOR EACH ROW
BEGIN
    Set new.SomeDate := now();
    
    -- Trigger Sets SomeDate to be current DateTime of Database
END;

 Some Additional Information worth Considering

Ok, this makes sense to me. I'll give it a shot and see what happens. Thanks :)

TinyPond, I am not understanding how the trigger does the offset of reducing the time by 5 minutes...Again, one of those I feel like the village idiot again LOL

Lets say the column in your table is call "sometime" and every time a new record is created in this table you want the column sometime to be the current date  minus five minutes.  you  would construct a trigger on the table and it would be  named "tr_somename"  and before  any record was ever inserted into your table the trigger would fire. Inside the trigger you would set somedate =now();  that would be the current date and time. The five minute offset you have talked about would be a calculation that would be something like somedate=dateAdd(now(), interval minute, -5)  The exact syntax was in the first time I replied to this post. 

Here is another example let say you executed the following sql statement  pretend the following table also had our trigger with our somedate column along with column named "anothervalue"

If you executed the following sql statment

Insert into mytable set anotherValue = 100;

and then Select * from myTable

you would see the following

anothervalue         someDate

100                       10/11/09 or current datetime you executed the sql statement.

I hope this helps, and would appreciate you marking this as the correct answer.

Regards

-Tiny

Ok, I think I'm pretty close here. I wrote the trigger but MySQL complains stating:

MySQL said: 

#1303 - Can't create a TRIGGER from within another stored routine

How can I add the trigger and get it to fire within the Stored Proc? I'll keep looking for solutions while I await your answer. I thank you for all your help :)

Correct a trigger is attached to your table,  you tell it when to run 

before of after

Insert, update, delete

It is invoked when these events happen 

so if inside your stored procedure you insert a record into a table then the before  or after insert triggers are fired. You can have both a before and insert trigger on insert or update. but you cannot have multiple before insert triggers on the same table in MySQL

Look up the following mysql triggers examples

                                 mysql stored procedure examples

they are totally different and used for different purpose. If I wanted to put a createdate time column on a table and update it no matter what application inserted records in a table I would set a trigger on the table this would allow any application to insert into this table and the createdate time would always be correct.

Well, I came up with an idea that may actually work a little better. I'm got a query that calculates the time stamp diff between the date from the raw XML and the time stamp that gets set during the record insertion. The query so far is:

SELECT TIMESTAMPDIFF(SECOND,MAX(ewise_slice.slice_timestamp),
MAX(ewise_raw_data_in.raw_data_datetime)) AS SECONDS
from ewise_raw_data_in,ewise_slice

Now, would you suggest placing this into a trigger? I do appreciate all your help with this; I'm learning a great deal about MySQL than I have previously known :)