I have a screenshot table and I want to get the user screenshot time starts and screenshot time ends. I want to create a query to be able to export the data to provide to my users.
Let's say this is my table data.
| scs_id | scs_tracker_id | created_at |
|---|---|---|
| 1 | 1000 | 2022-02-22 00:00:00 |
| 2 | 1001 | 2022-02-22 04:00:00 |
| 3 | 1000 | 2022-02-22 01:00:00 |
| 4 | 1002 | 2022-02-22 12:00:00 |
| 5 | 1001 | 2022-02-22 08:00:00 |
| 3 | 1000 | 2022-02-22 02:00:00 |
My expected output should be:
| scs_tracker_id | screenshot_starts | screenshot_ends |
|---|---|---|
| 1000 | 2022-02-22 00:00:00 | 2022-02-22 02:00:00 |
| 1001 | 2022-02-22 04:00:00 | 2022-02-22 08:00:00 |
| 1002 | 2022-02-22 12:00:00 | 2022-02-22 12:00:00 |
Code that I'm playing as of the moment:
SELECT
(SELECT MIN(created_at) FROM screen_shots GROUP BY scs_tracker_id ORDER BY scs_id ASC LIMIT 1) AS screenshot_starts,
(SELECT MAX(created_at) FROM screen_shots GROUP BY scs_tracker_id ORDER BY scs_id DESC LIMIT 1) AS screenshot_ends
FROM screen_shots
CodePudding user response：
Aggregate by tracker ID and then take the min/max timestamp:
SELECT
scs_tracker_id,
MIN(created_at) AS screenshot_starts,
MAX(created_at) AS screenshot_ends
FROM screen_shots
GROUP BY scs_tracker_id;