Generating totals from delta values in MySQL

[nelsonab]

Here's a little stored function I wrote for MySQL which will total a value stored as a delta. The function will only work with the history table, however that can easily be modified.

DELIMITER $$

DROP FUNCTION IF EXISTS `total_from_delta` $$
CREATE FUNCTION `total_from_delta`(itemid int, startdate date, days int) RETURNS bigint(20)
#Returns an approximated total from a delta stored item
#itemid = numeric id of the item to total
#startdate = String representation of the start date.
#  YYYY-MM-DD unless MySQL is setup differently
#  the startdate used will be equal to or greater than startdate
#days = Number of days to total over
#  If the total goes beyond the amount of actual data the last data point
#  will be used.
BEGIN
declare starttime,endtime int;
declare nearendtime int; #the previous to last timestamp
declare calcendtime int;
declare total bigint default 0;
declare prevtime,cur_time, val int;  #variables used to track the total
                                     #cur_time is used because curtime is reserved

declare done int default 0;  #setup the cursor stuff
declare cursor1 cursor for select h.clock,h.value from history h where
  h.clock>=starttime and h.clock<=endtime and h.itemid=itemid;
#declare continue handler for not found set done = 1;  #moved below but kept here for documentation

set calcendtime = unix_timestamp(timestampadd(day,days,startdate));

select h.clock into starttime from history h where
  h.itemid=itemid and h.clock>=unix_timestamp(startdate) limit 1;

select h.clock into endtime from history h where
  h.itemid=itemid and h.clock>=calcendtime limit 1;

select h.clock into nearendtime from history h where
  h.itemid=itemid and h.clock>=starttime and h.clock<=calcendtime
  order by clock desc limit 1;

set endtime = ifnull(endtime,nearendtime);  #set the end time as needed

begin  #we need to create a begin/end block in case a previous query
       #returned an empty result set and gorked the handler.
declare continue handler for sqlstate '02000' set done = 1;

open cursor1;

fetch cursor1 into prevtime, val;  #preload the variables

repeat
  fetch cursor1 into cur_time,val;
  if not done then
    set total = total + (val * (cur_time - prevtime));
  end if;
  set prevtime=cur_time;
until done end repeat;

close cursor1;
end;

return total;

END $$

DELIMITER ;

To call the function do the following:
select total_from_delta(itemid,'YYYY-MM-DD',days);

If you're using it in a script or other query it is suggested you cast it to a more understandable name such as:
select total_from_delta(1847,'2008,12-15',15) as total;

Edit:
If you see a warning after executing the function it can safely be ignored. It is saying that it could not find an end point which equaled startdate + days.

[nelsonab]

I made a few changes to the function. It will now work with negative time values. If you provide a negative day value then the starttime will be interpreted as the ending time.

CREATE FUNCTION `total_from_delta`(itemid int, startdate date, days int) RETURNS bigint(20)
BEGIN
declare starttime,endtime, startt, endt int;
declare nearendtime int; #the previous to last timestamp
declare calctime int;
declare total bigint default 0;
declare prevtime,cur_time, val int;  #variables used to track the total
                                     #cur_time is used because curtime is reserved

declare done int default 0;  #setup the cursor stuff
declare cursor1 cursor for select h.clock,h.value from history h where
  h.clock>=starttime and h.clock<=endtime and h.itemid=itemid;
#declare continue handler for not found set done = 1;  #moved below but kept here for documentation

#calculate our end point
set calctime = unix_timestamp(timestampadd(day,days,startdate));
if days>0 then
  select h.clock into starttime from history h where
    h.itemid=itemid and h.clock>=unix_timestamp(startdate) limit 1;

  select h.clock into endtime from history h where
    h.itemid=itemid and h.clock>=calctime limit 1;

  select h.clock into nearendtime from history h where
    h.itemid=itemid and h.clock>=starttime and h.clock<=calctime
    order by clock desc limit 1;

  set endtime = ifnull(endtime,nearendtime);  #set the end time as needed
else
  select h.clock into starttime from history h where
    h.itemid=itemid and h.clock>=calctime limit 1;

  select h.clock into endtime from history h where
    h.itemid=itemid and h.clock>=(unix_timestamp(startdate)) limit 1;

  select h.clock into nearendtime from history h where
    h.itemid=itemid and h.clock>=starttime and h.clock<=unix_timestamp(startdate)
    order by clock desc limit 1;
end if;

begin  #we need to create a begin/end block in case a previous query
       #returned an empty result set and gorked the handler.
declare continue handler for sqlstate '02000' set done = 1;

open cursor1;

fetch cursor1 into prevtime, val;  #preload the variables

repeat
  fetch cursor1 into cur_time,val;
  if not done then
    set total = total + (val * (cur_time - prevtime));
  end if;
  set prevtime=cur_time;
until done end repeat;

close cursor1;
end;

return total;

END