# mysql query to get time difference

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 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

• Why those two rows? What is their criteria? Oct 24, 2018 at 3:46
• its just for example, not just those two rows but for further rows I want to calculation like that Oct 24, 2018 at 3:50
• Not good enough. You can't ask for a difference for two arbitrary rows that have a black box around them when it shows up on stack overflow, there must be some criteria for this. Did your last post get deleted because it wasn't specific enough? Oct 24, 2018 at 3:55
• yes and I make it again to be easier to understand, can you solve this ? Oct 24, 2018 at 3:59
• So the relation ajsu_ID 4 and 9 row? Sequential (based on what order)? Is 4 always after 9. Do they have to be the same user_ID, tanggal etc.? Oct 24, 2018 at 4:03

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.

• thanks for your replying, but can you give me a full code because I tried your code into my code and give me some error Oct 24, 2018 at 6:38
• I'm sorry - I had one syntax, one typographical and one logic error. I have corrected the SQL in the answer. I have also added a link at the end of the answer where you can see this running on db-fiddle (ie, here: db-fiddle.com/f/5P6k9qMNP8UCoGHuQe1J2G/1 ) Oct 25, 2018 at 0:28