4

I'm a bit stuck at my Bachelor's thesis and hope you can help me. In order to evaluate a photovoltaic system I need to calculate the difference between total energy amounts. These are automatically updated in a MySQL-table with a timestamp, but without an id-number. I need to get the delta/difference between those energy amounts automatically as a extra column to visualize it in Grafana.

******************************************
Timestamp            | SB1_AC_Total       | Needed information (delta)
******************************************
2020-06-24 09:32:45  | 11.326.302         |   23

2020-06-24 09:32:02  | 11.326.279         |   22

2020-06-24 09:31:20  | 11.326.257         |   ...

This list goes on for weeks. I really hope you can help me, because I have no idea and it is the first time I work with MySQL.

P.Salmon
  • 17,104
  • 2
  • 12
  • 19
Viking_03
  • 43
  • 4
  • 1
    Similar question: https://stackoverflow.com/questions/13196190/mysql-subtracting-value-from-previous-row-group-by – Hrishabh Gupta Jun 24 '20 at 07:50
  • Can you explain 11.326.302 - this is not a numbering system I recognise. – P.Salmon Jun 24 '20 at 07:51
  • The points are just for a better overview. The number is 11326302 and the unit is Wh. – Viking_03 Jun 24 '20 at 07:57
  • Less elegant but is it maybe possible to store the previous data of SB1_AC_Total in a second column at the time SB1_AC_Total is updated? In this case I could just print the difference between the two columns in Grafana. – Viking_03 Jun 24 '20 at 09:47

3 Answers3

0

Consider following code and use it as example:

create table test (time timestamp, total int);

insert into test values (current_timestamp() - interval 2 day, 100);
insert into test values (current_timestamp() - interval 1 day, 120);
insert into test values (current_timestamp(),                  125);

select i.*, i.total - t_outer.total as diff 
from (select t.*, (select max(time) 
                   from test t1 where t1.time < t.time) as last_timestamp
      from test t
) as i 
left join test t_outer
on i.last_timestamp = t_outer.time;

As result you get something like:

time    total   last_timestamp  diff
"2020-06-23 10:58:21"   120 "2020-06-22 10:58:19"   20
"2020-06-24 10:58:22"   125 "2020-06-23 10:58:21"   5
"2020-06-22 10:58:19"   100 NULL    NULL

EDIT:

If you want to put the diff value into a new table column, you can do it like this:

alter table test add column diff int default null;

create table select_bkp as -- you know the code below already
select i.time, i.total - t_outer.total as diff 
from (select t.*, (select max(time) 
                   from test t1 where t1.time < t.time) as last_timestamp
      from test t
) as i 
left join test t_outer
on i.last_timestamp = t_outer.time;

SET SQL_SAFE_UPDATES = 0; -- so all rows can be updated at once

update test t
join select_bkp b
  on t.time = b.time
set t.diff = b.diff;

SET SQL_SAFE_UPDATES = 1; -- enable secure updates
Marek Puchalski
  • 3,286
  • 2
  • 26
  • 35
  • Less elegant but is it maybe possible to store the previous data of SB1_AC_Total in a second column at the time SB1_AC_Total is updated? In this case I could just print the difference between the two columns in Grafana. – Viking_03 Jun 24 '20 at 09:57
  • Based on the query above you may run an update script. This would improve the later query time. Do you need an SQL statement to do this, or can you write it yourself? – Marek Puchalski Jun 24 '20 at 10:02
  • Unfortunately I can't. I know the basics of C, but I not really good at programming. My thesis will work on the evaluation of the PV and not programming, so this task is more preparation for what I want to do with the date. – Viking_03 Jun 24 '20 at 10:07
  • It would be very kind if you could help me out. – Viking_03 Jun 24 '20 at 10:08
  • I have edited the post. I have created a temporary table to make the SQLs more readable. Feel free to delete `select_bkp` after the migration is made. – Marek Puchalski Jun 24 '20 at 15:02
0

I was able to answer my question with the following code:

ALTER TABLE TABLENAME  ADD SB1_AC_GES_DIFF INT;
DELIMITER $$
CREATE TRIGGER TABLENAME_Trigger
    BEFORE INSERT
    ON TABLENAME FOR EACH ROW
BEGIN
   DECLARE SB1_AC_GES_old INT;  
  SELECT max(SB1_AC_GES) INTO SB1_AC_GES_old FROM TABLENAME;
   SET NEW.SB1_AC_GES_Diff = New.SB1_AC_GES - SB1_AC_GES_old;
END$$    
DELIMITER ;
Viking_03
  • 43
  • 4
-1

How you approach this depends on your skill set with various tools. I, for example, am old in 'C' but green in SQL.

Now: Assuming that you have a database already, and want to create a new one with the extra field in it, this is broadly how I would approach it.

Create a new empty database with the fields you want in it.

open both databases 

Read all the old database into a program coded in whatever language you are happy with. 
Typically you will access this by executing an SQL statement like

SELECT * from table1 order by timestamp

In general you can now step through the data base line by line. 
The algorithm you want is this

set a variable 'delta' to zero
set a variable 'flag to zero
while(more lines)
    read 'timestamp'
    read 'SB1_AC_Total'
    write 'timestamp', 'SB1_AC_Total', and 'delta' to the new database using sql INSERT command.
    If 'flag' is not zero, set 'delta' to ('SB1_AC_Total' - 'old_data');  endif
    set 'flag' to 1
    set a variable 'old_data' to 'SB1_AC_Total'
endwhile

This will duplicate the database with a new column whose value is the current value minus the previous value.

I suspect there is an SQL way using two selects to generate two temporary tables and then reading from one and the other and combining the result into a third, but its beyond me.

Leo Smith
  • 124
  • 4