id | coordinatorId | time |
+——+———–+———+—————+———————+
| 5114 | 1 | 2024-05-30 11:13:30 |
| 5113 | 1 | 2024-05-30 10:13:22 |
| 5112 | 1 | 2024-05-30 09:13:14 |
| 5111 | 1 | 2024-05-30 08:13:02 |
| 5110 | 1 | 2024-05-30 07:12:52 |
| 5109 | 1 | 2024-05-30 06:12:38 |
| 5108 | 1 | 2024-05-30 05:12:33 |
| 5107 | 1 | 2024-05-30 04:12:29 |
| 5106 | 1 | 2024-05-30 03:12:17 |
| 5105 | 1 | 2024-05-30 02:12:09 |
| 5105 | 2 | 2024-05-30 02:12:09 |
+——+———–+———+—————+———————+
`
Tried
SELECT DISTINCT coordinatorId FROM coordinator WHERE (coordinator.time < now() – INTERVAL 180 minute) NOT IN (coordinator.time > now() – INTERVAL 180 minute);
My devices report in every hour. How do I find the coordinatorId that has not reported in , in the last 3 hours, the problem is that it locates anything that was less then 3 hours which is included in coordinatorId = 1, it should only find coordinatorId 2.
With a number of variations. Nothing works.
Any Help?
Thanks,
Donald
sodcore is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.