mysql: difference between values in one column

Question

this board helped me a few times in the past.

My challange: I want to get the difference between the values within one column.

The table looks like this:

id  |  channel_id  |  timestamp   |  value
4515|    7         |1519771680000 |  7777
4518|    8         |1519772160000 |  6666
4520|    7         |1519772340000 |  8888

• id: Internal ID from Datasource. In some cases it's ordered, in other cases not. We cannot thrust this order.
• channel_id: Different data sources.
• timestamp: unix timestamp.
• value: measured value.

What I want to do:

Filter (e.g. channel_id = 7). Calculate the difference between one timestamp and the next one. In this example: 8888-7777

I found an solution on another database but I cannot transfer it to mysql as the windows functions are very limited. Has somebody of you an idea how to get a solution which can be used in select statements?

Thx and KR Holger

Answer 1

thanks for all your support. I tried a lot and created "my" solution based on a stored procedure. It is not as performant as it could be but it delivers the required values.

The code is running in a loop with a max size of repetitions in the script execution to avoid an endless step :)

#Auswahl größer CH10-Wert
set @var_max_ch10vz =
    (
    select max(data.timestamp)
    from volkszaehler.data 
    where data.channel_id=10
    )
;
#Auswahl kleinster offener Wert aus SBFSPOT
set @var_min_sbfspot = 
    (
    select min(data.timestamp_unix*1000)
    from sbfspot_u.data
    where 
        data.timestamp_vzjoin is null 
        and data.timestamp_unix >1522096327
        and data.timestamp_unix*1000 < @var_max_ch10vz
    )
;
#Abgleich gegen VZ von unten
set @var_max_vz =
    (
    select min(data.timestamp)
    from volkszaehler.data 
    where data.channel_id=10 and data.timestamp >= @var_min_sbfspot
    )
;
#Abgleich gegen VZ von oben
set @var_min_vz =
    (
    select max(data.timestamp)
    from volkszaehler.data 
    where data.channel_id=10 and data.timestamp <= @var_min_sbfspot
    )
;
#Auswahl join Zeitstempel
set @vz_join_timestamp =
    (
        select tmp.uxtimestamp 
        from (
            select @var_max_vz as uxtimestamp, abs(@var_min_sbfspot-@var_max_vz) as diff
            UNION
            select @var_min_vz as uxtimestamp, abs(@var_min_sbfspot-@var_min_vz) as diff
            ) tmp
        order by tmp.diff asc
        limit 1
    )
;