Calculate difference in dates using JOIN in MySql

Question

I have MySQL table which has logId as the foreign key

The other table has logId as one column and corresponding startDate and endDate

I want to write a JOIN in a way such that my output provides difference between startDate and endDate (in hours) for every server.

I have written the following JOIN but need help to calculate to the difference in dates and show it as TotalTime

select server, totaltime from table1
inner join table2 on table1.logId = table2.logId

Asked before: https://stackoverflow.com/questions/10907750/calculate-difference-between-two-datetimes-in-mysql — Luuk, Jan 10 '20 at 07:15
convert the dates unix timestanps and subtract/divide to reach hours — Caius Jard, Jan 10 '20 at 07:18
SELECT "test_column", (UNIX_TIMESTAMP('2010-01-10 18:16:55') - UNIX_TIMESTAMP('2010-01-10 17:16:55')) / 60 / 60 as total_time https://www.mysqltutorial.org/tryit/ — Strauteka, Jan 10 '20 at 07:28

score 3 · Accepted Answer · edited Jan 10 '20 at 07:51

3

SELECT 
    t1.server, 
    TIMESTAMPDIFF(HOUR,t2.StartDate, t2.EndDate) AS TotalTime
FROM 
    table1 t1
    LEFT JOIN table2 t2 ON t1.logId = t2.logId

edited Jan 10 '20 at 07:51

mitkosoft

5,262
1
13
31

answered Jan 10 '20 at 07:30

kyu

46
3

score 0 · Answer 2 · answered Jan 10 '20 at 07:29

0

The function TIMEDIFF gives the difference between two time/datetime

SELECT 
    servername, TIMEDIFF(EndDate, StartDate) AS totaltime
FROM
    table1
        INNER JOIN
    table2 ON table1.logId = table2.logId

answered Jan 10 '20 at 07:29

Rakesh Shewale

497
6
22

Calculate difference in dates using JOIN in MySql

2 Answers2