Hello I am trying for a moment to get the time difference between each activity and activity 1 but I only succeed by filling in a particular activity and therefore obtain each line with this difference.

select 
  name, 
  TIMESTAMPDIFF(SECOND, 
                (select ts 
                         from feed 
                         where TeamId=1 and ActivityId=1), 
                 (select ts 
                    from feed 
                    where TeamId=1 and ActivityId=4)) 
from activity 
inner join feed on feed.ActivityId = activity.id 
where TeamId=1 order by FeedId DESC;

 ------ -----------------------------------------------------  
| act4 |                                                1105 | 
| act3 |                                                1105 | 
| act3 |                                                1105 | 
| act2 |                                                1105 | 
| act1 |                                                1105 |
 ------ -----------------------------------------------------  

I want my final results to look like this:

 ------ -----------------------------------------------------  
| name |                                       TIMESTAMPDIFF |
 ------ -----------------------------------------------------  
| act4 |                                                1105 | 
| act3 |                                                11   | 
| act3 |                                                11   | 
| act2 |                                                1    | 
| act1 |                                                0    | 
 ------ -----------------------------------------------------  

I would therefore like to obtain on each line the time difference between the activity selected by the select and activity 1. does anyone have an idea how to go about it?

This is feed :

 -------- --------------------- ------------ -------- 
| FeedId | ts                  | ActivityId | TeamId |
 -------- --------------------- ------------ -------- 
|      1 | 2022-12-20 16:21:30 |          1 |      1 |
|      2 | 2022-12-20 16:21:30 |          1 |      2 |
|      3 | 2022-12-20 16:21:30 |          1 |      3 |
|      4 | 2022-12-20 16:21:30 |          2 |      1 |
|      5 | 2022-12-20 16:21:30 |          3 |      1 |
|      6 | 2022-12-20 16:21:30 |          2 |      2 |
|      7 | 2022-12-20 16:38:54 |          3 |      1 |
|      8 | 2022-12-20 16:39:55 |          4 |      1 |
 -------- --------------------- ------------ -------- 

and this is activity :

 ---- -------------- ------ ------- 
| id | localisation | name | point |
 ---- -------------- ------ ------- 
|  1 | Madras       | act1 |  -650 |
|  2 | Valparaiso   | act2 |   450 |
|  3 | Amphi        | act3 |    45 |
|  4 | Amphix       | act4 |  4589 |
 ---- -------------- ------ ------- 

CodePudding user response：

I must have made an error copying the source.... but this looks pretty close:

select
   name,
   f1.ts time1,
   f4.ts time4,
   TIMESTAMPDIFF(SECOND,f1.ts,f4.ts)
from activity a
left join feed f1 on f1.ActivityId = 1 and f1.TeamId=1
left join feed f4 on f4.ActivityId = a.id and f4.TeamId=1
order by name  desc;

see: DBFIDDLE

output of this is: