Average of differences between 2 numbers located in 2 different tables

Question

I have a SQL problem that, I think, go far beyond my current knowledge. I have two tables and in each 2 dates in timestamp. I would like to have the average time between those 2 dates, average involving all fields entered in the tables.

I hope I would be clearer doing a simplification of my SQL tables and what I want to do :

-- Table 1 : study
-- -- id
-- -- timestamp (date of input, start of the study for instance)

-- Table 2 : study_monitoring
-- -- id
-- -- id_study
-- -- timestamp_2 (start of the first stage, for instance)

I want to get the average time between timestamp and timestamp_2.
I have first to link the study_monitoring field to its proper study field thanks to the ID located in table 2.

Then to do the difference between the 2 timestamps (the second being always superior to the other). Repeating this for all the fields and finally get the average.

Doing it in several SQL requests wouldn't bother me, it would be in a simple PHP function that return the average of the difference of timestamp.

I don't really know where to start, does the modelling of my tables allow such computing ?
Thanks in advance !

You need to do a `join` of the two tables to get the pairing of the numbers per row so you can take the differences, then take an aggregate average. But what do you mean by *average time between `timestamp` and `timestamp_2`*? Do you just mean the difference between the time stamps in minutes or something else? That's a place to start. Go off and work some ideas and come back with a more specific question. — lurker, Feb 17 '18 at 16:13
[take a look at post here may help you](https://stackoverflow.com/questions/14895782/average-difference-between-two-dates-grouped-by-a-third-field) — sam, Feb 17 '18 at 16:37

score 1 · Answer 1 · answered Feb 17 '18 at 16:28

If I understand correctly:

select s.id,
       avg(to_seconds(sm.timestamp_2) - to_seconds(s.timestamp)) as avg_in_seconds
from study s left join
     study_monitoring sm
     on s.id = sm.id_study
group by s.id;

This assumes that seconds are a reasonable unit of time. There are other ways to get other units or to convert this back to an interval representation.

score 0 · Answer 2 · answered Feb 17 '18 at 16:22

0

This will give you the average time difference in seconds.

SELECT Avg(DATEDIFF(second, s.timestamp, sm.timestamp_2))[AverageDifferenceSeconds]
FROM study s inner join study_monitoring sm on s.id=sm.id_study

answered Feb 17 '18 at 16:22

Andy

25
3

Your functions and syntax are for SQL Server, not MySQL. – Gordon Linoff Feb 17 '18 at 16:28
Yes, I did miss that it was for MySQL, yours is a good answer. – Andy Feb 17 '18 at 16:47

score 0 · Answer 3 · edited May 08 '18 at 05:22

Given the following schema:

CREATE TABLE `Study` ( `Id` INT NOT NULL AUTO_INCREMENT , `Start` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP , PRIMARY KEY (`Id`)) ENGINE = InnoDB;

CREATE TABLE `Study_Monitoring` ( `Id` INT NOT NULL AUTO_INCREMENT , `Study_Id_FK` INT NOT NULL, `StartPhase` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP , PRIMARY KEY (`Id`)) ENGINE = InnoDB;

INSERT INTO `Study` (`Start`) 
VALUES (DATE("2016-05-25")),(DATE("2016-05-25")),(DATE("2016-05-25")),(DATE("2016-05-25")),(DATE("2016-05-25"));

INSERT INTO `Study_Monitoring` (`Study_Id_FK`, `StartPhase`) 
VALUES (0, NOW()), (1, NOW()),(2, NOW()),(3, NOW()),(4, NOW())

You would get the average diff in seconds (assuming this is MySQL) with the following command:

SELECT  AVG(TIMESTAMPDIFF(second, Table_1.Start, Table_2.StartPhase))
FROM Study AS Table_1 
  INNER JOIN 
    Study_Monitoring AS Table_2 
      ON Table_1.Id = Table_2.Study_Id_FK

TIMESTAMPDIFF is described here to accept the units

FRAC_SECOND (microseconds), SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR.

Feel free to change it as appropriate. :)

See fiddle

score 0 · Answer 4 · answered Feb 17 '18 at 16:41

You don't specify the granularity you want for the diff. This does it in days:

select username, avg(end_date - start_date) as avg_days
from mytable
group by username

If you want the difference in seconds, use datediff():

select username, avg(datediff(ss, start_date, end_date)) as avg_seconds
...

datediff can measure the diff in any time unit up to years by varying the first parameter, which can be ss, mi, hh, dd, wk, mm or yy.

Average of differences between 2 numbers located in 2 different tables

4 Answers4