MySQL – Query to Get Time Difference

mariadbMySQLmysql-5.6query

I have query like this

SELECT user_ID, tanggal, kode_layanan, nomor_antrian, aksi_ID,jam 
FROM `transaksi` 
WHERE tanggal = '2018-10-11' 
  AND kode_layanan = 'A' 
  AND aksi_ID BETWEEN 4 AND 9

and the result is like the picture below

enter image description here

what I want to do is do calculation time difference "jam". I know there are similar question, but i think its difference condition. so how to solve query ? thanks

Best Answer

I think your column definition will be as shown in this query:

select
    user_ID,
    tanggal,
    kode_layanan,
    nomor_antrian,
    aksi_ID,
    jam,

    case aksi_ID
        when 4 then
            timediff (
                jam,
                (
                    select t2.jam
                    from transaki t2
                    where t2.aksi_ID = 9
                    and t2.nomor_antrian = 
                        (
                            select max(t3.nomor_antrian)
                            from transaki t3
                            where t3.nomor_antrian < transaki.nomor_antrian
                        )
                )
            )
        else
            null
    end as idle_time

from transaki
order by jam;

The logic is: "if the current row has an aski_ID value of '4' then find the time difference between the current row's jam value, and the jam value of the row having an aski_ID value of 9 and a nomor_antrian value that is the maximum nomor_antrian value less than the current row's nomor_antrian value".

You can see this result working on db-fiddle.

Related Question