Get time difference between two tables only for rows with the same 'id' - MySQL

124
February 20, 2021, at 1:40 PM

I ned to ged time difference between tableA and tableB for rows with the same id. Therefore id 10 and 11.

tableA:
id  book  scandate
----------------------
10  AA    '2021-01-15 08:52:05'
11  BB    '2021-01-15 08:55:15'
12  CC    '2021-01-15 08:56:23'
13  DD    '2021-01-15 09:01:02'
tableB:
id  book  scandate
----------------------
10  AA    '2021-01-15 08:52:15'
11  BB    '2021-01-15 08:55:28'
14  EE    '2021-01-15 08:56:23'
15  FF    '2021-01-15 09:01:02'

How can I do that? I know I can use timediff function but I dont know how to fetch wanted data into given function.

UPDATE: I forgot to mention that I need average value for timediff results.

select AVG(T.scandateDiff) from (
select tableA.id, tableA.book, tableA.scandate as scandateA, tableB.scandate as scandateB,
       timediff(tableB.scandate, tableA.scandate) as scandateDiff
from 
tableA 
join
tableB
on tableA.id = tableB.id) as T
Answer 1

You can use join to combine the table and then whatever function you want for the difference:

select a.*, b.*,
       timediff(a.scandate, b.scandate)
from a join
     b
     on a.id = b.id
Rent Charter Buses Company
READ ALSO
Laravel MongoDB cannot get belongsTo relationship properly value

Laravel MongoDB cannot get belongsTo relationship properly value

When using jenssegers/laravel-mongodb, the belongsTo relationship properly always returns null, despite, when dumping, the value appearingHow can I get the belongsTo relationship?

180
Jetpack Paging 3 inside java/kotlin module

Jetpack Paging 3 inside java/kotlin module

I'm developing an app with clean architecture principlesI've a domain module which is a Java/Kotlin module and it hasn't android dependencies and a domainImpl module which is an Android module and has dependencies to local, remote and domain module

146
How to set a delay in Postam Runner using a csv file as input

How to set a delay in Postam Runner using a csv file as input

I'm using postman to test an API which is as follows: (this is a POST)

161