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 |
+----+--------------+------+-------+
1条答案
按热度按时间2q5ifsrm1#
I must have made an error copying the source.... but this looks pretty close:
see: DBFIDDLE
output of this is:
| name | time1 | time4 | TIMESTAMPDIFF(SECOND,f1.ts,f4.ts) |
| ------------ | ------------ | ------------ | ------------ |
| act4 | 2022-12-20 16:21:30 | 2022-12-20 16:39:55 | 1105 |
| act3 | 2022-12-20 16:21:30 | 2022-12-20 16:21:30 | 0 |
| act3 | 2022-12-20 16:21:30 | 2022-12-20 16:38:54 | 1044 |
| act2 | 2022-12-20 16:21:30 | 2022-12-20 16:21:30 | 0 |
| act1 | 2022-12-20 16:21:30 | 2022-12-20 16:21:30 | 0 |