Storing variables in DB for later recall

[dbrewerton]

Hello folks, I've got one that seems a little tricky to me. What I want to do is to calculate my cumulative BTU measurements. I'm already storing various information in the database where I can calculate the BTU/Hr production. The formula to calculate Cumulative BTUs is as follows (per minute):

(BTU1+BTU2) / 2 * .0167

I calculate the BTU/Hr production using this formula:

(t2-t1) * flow rate * brine factor * system status

(38.72 - 36.77) * 14.5 * 485 * 1 = 13713.375 BTU/Hr.

Now, what I want to do is to calculate these BTU/Hr production measurements on the fly. I will have different devices sending data in at different time slices. To help clarify, my table structure is shown below:

slices
       |
       > slice_id
       > device_id
       > timestamp

slice_msmsnt
       |
       > msmnt_id
       > slice_id
       > msmnt_type_id
       > msmsnt_value

The common field between those two tables is slice_id. Any assistance with this would be greatly appreciated.

[code green]

What are you using as a front end?
Can you do the calculations in there?

[dbrewerton]

Actually I figured out the algorithm when I call my stored procedure. The problem I have now is trying to handle a NULL value. This is the block of code that its hanging up on:

select MAX(id) from ewise_cumulative_btus where device_id = x_device_id into lastbtuid;
select curbtus from ewise_cumulative_btus where id = lastbtuid INTO lastbturead;
select (btucalc + lastbturead) INTO lastcurbtus;

# right here is where I'm getting messed up...what should I use to handle NULL?
if (lastcurbtus = NULL) then
insert into ewise_cumulative_btus (device_id,curbtus,cumul_btus) values(x_device_id,btucalc,0);
else
insert into ewise_cumulative_btus (device_id,curbtus,cumul_btus) values(x_device_id,btucalc,lastcurbtus);
end if;

[code green]

Comprisons fall down where NULL is involved.
To test for a NULL value use IS NULL

[dbrewerton]

Ok, I figured that one out, I did this:

if (lastcurbtus IS NULL) then
	set lastcurbtus = 0;
end if;

My problem now though is it adds the first three rows fine but then it repeats the third rows entry.

device_id           |         curbtus            |         cumul_btus
10                     |              000            |                  0
10                     |              453            |                  453
10                     |              453            |                  906
10                     |              453            |                  906
10                     |              453            |                  906

Any idea what I'm doing wrong? Here's the block that does this part:

select MAX(id) from ewise_cumulative_btus where device_id = x_device_id into lastbtuid;
select curbtus from ewise_cumulative_btus where id = lastbtuid INTO lastbturead;
select (btucalc + lastbturead) INTO lastcurbtus;
if (lastcurbtus IS NULL) then
set lastcurbtus = 0;
end if;

insert into ewise_cumulative_btus (device_id,curbtus,cumul_btus) values(x_device_id,btucalc,lastcurbtus);
select x_device_id,btucalc,lastcurbtus;

[code green]

Your method is a little unconventional.
I can't see the problem directly.
You need to debug by performing each process in step and looking at the results at each step.
That should narrow the problem down

[dbrewerton]

Ok, let me try to explain the issue a little more clearly. I have a table named cumulative BTUs which in the table logic, I'm trying to get my SP to take the most recent cumulative BTU calculation and add it to the current BTU calc to come up with a new cumulative BTU count. Should be pretty straight forward but I'm not getting what I would expect. My procedure which does this math is shown below:

#Grabs current time slice
select slice_id from ewise_slice where device_id = x_device_id order by slice_id DESC LIMIT 1 into slice1;
#Grabs 2nd most recent time slice
select slice_id from ewise_slice where device_id = x_device_id order by slice_id DESC LIMIT 1,1 into slice2;

#Setting value time1 of most recent slice
select slice_timestamp from ewise_slice where slice_id = slice1 INTO time1;
#Setting value time1 of 2nd most recent slice
select slice_timestamp from ewise_slice where slice_id = slice2 INTO time2;
#Compare timestamps to get a time factor for calc
SELECT TIMESTAMPDIFF(MINUTE,time2,time1) into slicetimediff;
# if the value is null, set it to 1 for default
if (slicetimediff IS NULL) then
	set slicetimediff = 1;
end if;

#setting flowrate
select egw_con_flowrate from egw_constants where egw_con_device_id = x_device_id INTO flowrate;
#setting brine factor (ethanol is 485, pure water is 500)
select brine from egw_constants where egw_con_device_id = x_device_id INTO brinefactor;

#tempurature readings 1 and 2 plus system status for first time slice
select msmnt_value from ewise_slice_msmnt where slice_id = slice1 AND msmnt_type_id = '1' INTO t1_1;
select msmnt_value from ewise_slice_msmnt where slice_id = slice1 AND msmnt_type_id = '2' INTO t2_1;
select msmnt_value from ewise_slice_msmnt where slice_id = slice1 AND msmnt_type_id = '3' INTO c1_1;
		
#same for time slice 2
select msmnt_value from ewise_slice_msmnt where slice_id = slice2 AND msmnt_type_id = '1' INTO t1_2;
select msmnt_value from ewise_slice_msmnt where slice_id = slice2 AND msmnt_type_id = '2' INTO t2_2;
select msmnt_value from ewise_slice_msmnt where slice_id = slice2 AND msmnt_type_id = '3' INTO c1_2;

#calculate BTUHr production for slice 1
SELECT ABS(t2_1 - t1_1) * flowrate * brinefactor * c1_1 INTO BTUHr1;
#calculate BTUHr production for slice 2
SELECT ABS(t2_2 - t1_2) * flowrate * brinefactor * c1_2 INTO BTUHr2;

# If this is the first entry in the table for this device then the value will be null
# so set it to zero
IF (BTUHr2 IS NULL) then
	set BTUHr2 = 0;
end if;

# Now we calculate the btu output for the past two time slices
SELECT (BTUHr1+BTUHr2)/2*(slicetimediff/60) INTO btucalc;

# Grab most recent record id 
select id from ewise_cumulative_btus where device_id = x_device_id order by id desc LIMIT 1,1 INTO lastbtuid;

# If this is the first reading, this will calculate to NULL so set to 0
select ABS(cumul_btus) from ewise_cumulative_btus where id = lastbtuid INTO lastbturead;

if (lastbturead IS NULL) then
	set lastbturead = 0;
end if;

# Echo to screen to see what the values are - trying to add them together
# This seems to be what isn't working...
select (ABS(btucalc) + ABS(lastbturead)) INTO lastcurbtus;

# Now insert the values into the row - 
insert into ewise_cumulative_btus (device_id,curbtus,cumul_btus) values(x_device_id,btucalc,lastcurbtus);
select BTUHr1,BTUHr2,flowrate,brinefactor,x_device_id,btucalc,lastcurbtus;

I hope I documented the code well enough. Looking forward to your response :)

[dbrewerton]

Thought this may help. I'm going to include my entire SP plus the echoed results. What I'm finding is it is adding the cumulated BTUs from two records ago instead of using the last record. Take a look below:

mysql> DELIMITER $$
mysql> CREATE PROCEDURE `parse_rawtest2`()
    ->
    -> 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;
    ->
    -> DECLARE base_time DATETIME;
    -> DECLARE time_shift DATETIME;
    -> DECLARE time_diff BIGINT;
    -> DECLARE goodtime DATETIME;
    -> DECLARE xtextcopy text;
    ->
    -> # slice1 is active slice - slice2 is the previous slice - time1 and time2 relate to these respectively
    -> DECLARE slice1 bigint;
    -> DECLARE slice2 bigint;
    -> DECLARE time1 DATETIME;
    -> DECLARE time2 DATETIME;
    -> DECLARE slicetimediff BIGINT;
    ->
    -> #variables for BTUHr calc
    -> DECLARE flowrate BIGINT;
    -> DECLARE brinefactor BIGINT;
    ->
    -> #BTU/Hr calc 1
    -> DECLARE btuhr1 bigint;
    -> DECLARE t1_1 BIGINT;
    -> DECLARE t2_1 BIGINT;
    -> DECLARE c1_1 BIGINT;
    ->
    -> #BTU/Hr calc 2
    -> DECLARE btuhr2 BIGINT;
    -> DECLARE t1_2 BIGINT;
    -> DECLARE t2_2 BIGINT;
    -> DECLARE c1_2 BIGINT;
    ->
    -> #calculation of (BTUHr1+BTUHr2)* 2 * slicetimediff/60
    -> DECLARE btucalc bigint;
    ->
    -> #Insertion values for cumulative BTUs
    -> DECLARE lastbtuid BIGINT;
    -> DECLARE lastbturead BIGINT;
    -> DECLARE lastcurbtus 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, ewise_raw_data_in.raw_data_datetime FROM ewise_raw_data_in LIMIT 1 INTO x_id, xtext, goodtime;
    ->         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;
    ->
    -> #        select x_id, x_device_string, x_device_id;
    ->
    ->  IF (x_device_id = 0) THEN
    ->  INSERT INTO ewise_device (device_string) VALUES (x_device_string);
    ->  SELECT MAX(device_id) INTO x_device_id;
    ->  END IF;
    ->
    ->         CREATE TEMPORARY TABLE IF NOT EXISTS temp_slice LIKE ewise_slice;
    ->         set xtextcopy = xtext; # make a backup to check time
    ->         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 temp_slice VALUES (default, x_device_id, time_text);
    ->                 END IF;
    ->         END WHILE;
    ->         Select max(time_text) from temp_slice into base_time;
    ->         select TIMESTAMPDIFF(SECOND,time_text,goodtime) INTO time_diff;
    -> #       select goodtime, base_time, time_diff;
    ->         drop table temp_slice;
    ->
    ->         set xtext = xtextcopy;  # restore backup
    ->         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);
    ->                 select TIMESTAMPADD(SECOND,time_diff,time_text) into time_shift;
    ->                 INSERT INTO ewise_slice VALUES (default, x_device_id, time_shift);
    ->
    ->                 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;
    ->                          # Find out what the value being inserted into the msmnt table is
    ->                          # select default, new_slice_id, x_msmnt_type_id, value_text;
    ->                                 INSERT INTO ewise_slice_msmnt VALUES (default, new_slice_id, x_msmnt_type_id, value_text);
    ->                         END IF;
    ->
    ->                 END WHILE;
    ->
    ->          select slice_id from ewise_slice where device_id = x_device_id order by slice_id DESC LIMIT 1 into slice1;
    ->          select slice_id from ewise_slice where device_id = x_device_id order by slice_id DESC LIMIT 1,1 into slice2;
    ->
    ->          select slice_timestamp from ewise_slice where slice_id = slice1 INTO time1;
    ->          select slice_timestamp from ewise_slice where slice_id = slice2 INTO time2;
    ->
    ->          SELECT TIMESTAMPDIFF(MINUTE,time2,time1) into slicetimediff;
    ->          if (slicetimediff IS NULL) then
    ->                  set slicetimediff = 1;
    ->          end if;
    ->
    ->  SELECT slice1,time1,slice2,time2,slicetimediff;
    ->
    ->          select egw_con_flowrate from egw_constants where egw_con_device_id = x_device_id INTO flowrate;
    ->          select brine from egw_constants where egw_con_device_id = x_device_id INTO brinefactor;
    ->
    ->  select flowrate,brinefactor;
    ->
    ->          select msmnt_value from ewise_slice_msmnt where slice_id = slice1 AND msmnt_type_id = '1' INTO t1_1;
    ->          select msmnt_value from ewise_slice_msmnt where slice_id = slice1 AND msmnt_type_id = '2' INTO t2_1;
    ->          select msmnt_value from ewise_slice_msmnt where slice_id = slice1 AND msmnt_type_id = '3' INTO c1_1;
    ->
    ->          select msmnt_value from ewise_slice_msmnt where slice_id = slice2 AND msmnt_type_id = '1' INTO t1_2;
    ->          select msmnt_value from ewise_slice_msmnt where slice_id = slice2 AND msmnt_type_id = '2' INTO t2_2;
    ->          select msmnt_value from ewise_slice_msmnt where slice_id = slice2 AND msmnt_type_id = '3' INTO c1_2;
    ->
    ->          SELECT ABS(t2_1 - t1_1) * flowrate * brinefactor * c1_1 INTO BTUHr1;
    ->          SELECT ABS(t2_2 - t1_2) * flowrate * brinefactor * c1_2 INTO BTUHr2;
    ->
    ->  select BTUHr1,BTUHr2;
    ->
    ->          IF (BTUHr2 IS NULL) then
    ->                  set BTUHr2 = 0;
    ->          end if;
    ->
    ->          SELECT (BTUHr1+BTUHr2)/2*(slicetimediff/60) INTO btucalc;
    ->
    ->          select id from ewise_cumulative_btus where device_id = x_device_id order by id desc LIMIT 1,1 INTO lastbtuid;
    ->
    ->  select lastbtuid;
    ->
    ->          select ABS(cumul_btus) from ewise_cumulative_btus where id = lastbtuid INTO lastbturead;
    ->
    ->          if (lastbturead IS NULL) then
    ->                  set lastbturead = 0;
    ->          end if;
    ->
    ->  select lastbturead;
    ->
    ->          select (ABS(btucalc) + ABS(lastbturead)) INTO lastcurbtus;
    ->
    ->          insert into ewise_cumulative_btus (device_id,curbtus,cumul_btus) values(x_device_id,btucalc,lastcurbtus);
    ->
    ->  select x_device_id,btucalc,lastcurbtus;
    ->
    ->         END IF;
    ->
    ->         END WHILE;
    ->
    ->         #Delete the data row
    ->         DELETE FROM ewise_raw_data_in WHERE ewise_raw_data_in.raw_id=x_id;
    ->
    -> # DROP TABLE temp_slice;
    -> END LOOP main_loop;
    -> END
    ->
    -> $$
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql>
mysql> use egwtest;
Database changed
mysql> call parse_rawtest2(); #Performed on 5 records

+--------+---------------------+--------+---------------------+---------------+--------+--------+---------+-----------+-------------+-------------+---------+-------------+
| slice1 | time1               | slice2 | time2               | slicetimediff | BTUHr1 | BTUHr2 | btucalc | lastbtuid | lastbturead | x_device_id | btucalc | lastcurbtus |
+--------+---------------------+--------+---------------------+---------------+--------+--------+---------+-----------+-------------+-------------+---------+-------------+
|      6 | 2010-03-18 15:25:36 |      5 | 2010-03-18 15:29:36 |            -4 |  20370 |  20370 |   -1358 |         4 |         678 |          11 |   -1358 |        2036 |
+--------+---------------------+--------+---------------------+---------------+--------+--------+---------+-----------+-------------+-------------+---------+-------------+
1 row in set (0.01 sec)

+--------+---------------------+--------+---------------------+---------------+--------+--------+---------+-----------+-------------+-------------+---------+-------------+
| slice1 | time1               | slice2 | time2               | slicetimediff | BTUHr1 | BTUHr2 | btucalc | lastbtuid | lastbturead | x_device_id | btucalc | lastcurbtus |
+--------+---------------------+--------+---------------------+---------------+--------+--------+---------+-----------+-------------+-------------+---------+-------------+
|      7 | 2010-03-18 15:26:36 |      6 | 2010-03-18 15:25:36 |             1 |  20370 |  20370 |     339 |         5 |         848 |          11 |     339 |        1187 |
+--------+---------------------+--------+---------------------+---------------+--------+--------+---------+-----------+-------------+-------------+---------+-------------+
1 row in set (0.04 sec)

+--------+---------------------+--------+---------------------+---------------+--------+--------+---------+-----------+-------------+-------------+---------+-------------+
| slice1 | time1               | slice2 | time2               | slicetimediff | BTUHr1 | BTUHr2 | btucalc | lastbtuid | lastbturead | x_device_id | btucalc | lastcurbtus |
+--------+---------------------+--------+---------------------+---------------+--------+--------+---------+-----------+-------------+-------------+---------+-------------+
|      8 | 2010-03-18 15:27:36 |      7 | 2010-03-18 15:26:36 |             1 |  20370 |  20370 |     339 |         6 |        2036 |          11 |     339 |        2375 |
+--------+---------------------+--------+---------------------+---------------+--------+--------+---------+-----------+-------------+-------------+---------+-------------+
1 row in set (0.06 sec)

+--------+---------------------+--------+---------------------+---------------+--------+--------+---------+-----------+-------------+-------------+---------+-------------+
| slice1 | time1               | slice2 | time2               | slicetimediff | BTUHr1 | BTUHr2 | btucalc | lastbtuid | lastbturead | x_device_id | btucalc | lastcurbtus |
+--------+---------------------+--------+---------------------+---------------+--------+--------+---------+-----------+-------------+-------------+---------+-------------+
|      9 | 2010-03-18 15:28:36 |      8 | 2010-03-18 15:27:36 |             1 |  20370 |  20370 |     339 |         7 |        1187 |          11 |     339 |        1526 |
+--------+---------------------+--------+---------------------+---------------+--------+--------+---------+-----------+-------------+-------------+---------+-------------+
1 row in set (0.08 sec)

+--------+---------------------+--------+---------------------+---------------+--------+--------+---------+-----------+-------------+-------------+---------+-------------+
| slice1 | time1               | slice2 | time2               | slicetimediff | BTUHr1 | BTUHr2 | btucalc | lastbtuid | lastbturead | x_device_id | btucalc | lastcurbtus |
+--------+---------------------+--------+---------------------+---------------+--------+--------+---------+-----------+-------------+-------------+---------+-------------+
|     10 | 2010-03-18 15:29:36 |      9 | 2010-03-18 15:28:36 |             1 |  20370 |  20370 |     339 |         8 |        2375 |          11 |     339 |        2714 |
+--------+---------------------+--------+---------------------+---------------+--------+--------+---------+-----------+-------------+-------------+---------+-------------+
1 row in set (0.10 sec)

[dbrewerton]

Oddly enough, by looking at what I posted I realized it was referencing the 2nd id back because of a quirk in the code. Its working now so thank you for bearing with me :)

[dbrewerton]

I have a related but different question to ask so I'll start a new post.

[dbrewerton]

code_green, please check out my posting:

http://bytes.com/topic/mysql/answers/883944-calculating-existing-measurements-cumulative-btus#post3555477

Many thanks :)