# 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? – danblack Oct 24 '18 at 3:46
• its just for example, not just those two rows but for further rows I want to calculation like that – coder Oct 24 '18 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? – danblack Oct 24 '18 at 3:55
• yes and I make it again to be easier to understand, can you solve this ? – coder Oct 24 '18 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.? – danblack Oct 24 '18 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 – coder Oct 24 '18 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 ) – youcantryreachingme Oct 25 '18 at 0:28