TIMEDIFF() between records that have one value and the other, not just one

Question

I have a table which records the states of a project:

CREATE TABLE `project_states` (
    `id` INT(10) NOT NULL AUTO_INCREMENT,
    `state_id` INT(10) NULL DEFAULT NULL,
    `project_id` INT(10) NULL DEFAULT NULL,
    `created_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB

Some fake data:

    id  | state_id | project_id | created_at
-----------------------------------------------------
     1  |   2      |   8        | 2014-05-27 10:58:12   
     2  |   3      |   8        | 2014-05-27 11:10:34
     3  |   8      |   8        | 2014-05-27 11:56:48
     4  |   2      |  10        | 2014-05-27 11:08:34
     5  |   4      |  10        | 2014-05-27 11:59:01

I'm trying to get the time difference between two states (say, 2 and 8), and only for the projects which do have both states; in this case, only for project 8 (since 10 doesn't have the state 8).

So far I quite managed to select the projects which match the criteria (have both the values, not just one), but this query returns a tuple of results for each project matched:

SELECT t.*
   FROM (
     SELECT ps.*
     FROM project_states ps
     WHERE ps.state_id IN (2,8) 
   ) as t
JOIN project_states pro ON pro.project_id = t.project_id
WHERE pro.state_id = 8

which correctly returns:

    id  | state_id | project_id | created_at
-----------------------------------------------------
     1  |   2      |   8        | 2014-05-27 10:58:12   
     3  |   8      |   8        | 2014-05-27 11:56:48

I'm quite sure it works since if I add the missing state to the other project, it returns the new tuple of results:

    id  | state_id | project_id | created_at
-----------------------------------------------------
     1  |   2      |   8        | 2014-05-27 10:58:12   
     3  |   8      |   8        | 2014-05-27 11:56:48
     4  |   2      |  10        | 2014-05-27 11:08:34
     6  |   8      |  10        | 2014-05-27 12:03:08

But how do I calculate a time difference? I'm using PHP and I know I can loop over the results by project_id and then calculate the difference, but I think there might be a pure SQL solution that would yield me a result like:

project_id | difference
------------------------
     8     | 0000-00-00 01:02:00
    10     | 0000-00-00 01:05:26

Well, actually my goal would be to calculate the average time difference in which a project lies between these two selected states, so all the records could boil down to just one average value, but that might be a problem I figure out later.

Answer 1

If you have only 2 states you can use simple INNER JOIN. And TIMESTAMPDIFF() to get difference in minutes for example:

SELECT p.project_id,
       TIMESTAMPDIFF(MINUTE,p.created_at,p1.created_at) 
          as state_minutes_diff

FROM project_states as p
JOIN project_states as p1 
  ON p.project_id=p1.project_id
     AND p1.state_id=8  

WHERE p.state_id=3

SQLFiddle demo

To get average for all projects:

SELECT AVG(TIMESTAMPDIFF(MINUTE,p.created_at,p1.created_at))
          as AVG_state_minutes_diff
FROM project_states as p
JOIN project_states as p1 
  ON p.project_id=p1.project_id
     AND p1.state_id=8  
WHERE p.state_id=3

SQLFiddle demo

Answer 2

SELECT project_id,
       timediff(min(created_at), max(created_at)) as difference
FROM project_states
WHERE state_id IN (2,8) 
GROUP BY project_id 
having count(distinct state_id) = 2

Answer 3

I would do this using aggregation and a having clause:

select ps.project_id,
       timestampdiff(second, min(case when ps.state_id = 2 then created_at end),
                     max(case when ps.state_id = 8 then created_at)
                    ) as diff_in_secs

from project_states ps
where ps.state_id in (2, 8)
group by ps.project_id
having count(distinct ps.state_id) = 2;

You can also do this with joins:

select ps2.project_id, timestampdiff(second, ps2.created_at, ps8.created_at)
from project_states ps2 join
     project_states ps8
     on ps2.project_id = ps8.project_id and
        ps2.state_id = 2 and ps8.state_id = 8;

Answer 4

Actually, you don't need JOIN, because you can resolve the issue with specifying proper GROUP BY clause:

SELECT
  SEC_TO_TIME(AVG(times.atime)) AS avg_time
FROM
  (SELECT
    project_id,
    TIMEDIFF(MAX(created_at), MIN(created_at)) AS atime
  FROM
    project_states
  WHERE
    state_id IN (2,8)
  GROUP BY
    project_id
  HAVING
    COUNT(DISTINCT id)>1) as times

_{It seems I was late, but at least, that's because of fiddle that I made}