MySQL subtract two rows from column and places into an alias

[sonima]

I am working with Node-red and Mysql database. I don't know How to compare previous value with new value on each different devices then insert new value into Data column.
I mean subtract new value with previous value on each devices then insert that new value into the Data column.
Anyone help me please ?

[michaelblight]

Are you wanting to do it in MySQL or Node Red? It's probably easier in NR by just storing the previous values in the flow context (assuming you have enabled persistent context), and then calculating it before you insert the new row into MySQL.

If you're wanting to do it in MySQL you would have to join the table to itself to get the previous entry. Something along the lines of:

select current.value - previous.value
from mytable as current
inner join mytable as previous on previous.ID =
    (
        select mytable.ID
        from mytable
        where mytable.Device = current.Device
            and mytable.timestamp < current.timestamp
         order by mytable.timestamp desc
         limit 1
    )

[Colin]

A quick search in the node red flows library found this which might be what you want.

node-red-contrib-delta

[Colin]

@sonima

I am working with Node-red and Mysql database. I don't know How to compare previous value with new value on each different devices then insert new value into Data column.

If you do mean that you already have data in the table and want to add a difference column then that would generally be considered bad practice as you are storing derived data and you can run into all sorts of problems if the data gets out of phase, for example if data in the table has to be edited then you have to remember to adjust the derived values. Generally it is better to calculate the difference when you need it, possibly using the technique posted by @michaelblight.

[sonima]

Thanks @michaelblight

[sonima]

No, I don't have Data. I mean I need to calculate the Data from current value - previous value then insert in to Data column for each different devices.