15 lines
488 B
MySQL
15 lines
488 B
MySQL
|
-- Stale devices: enabled devices with no GPS fix in last 2 hours
|
||
|
|
SELECT
|
||
|
|
d.imei,
|
||
|
|
d.device_name,
|
||
|
|
lp.gps_time AS last_gps_time,
|
||
|
|
EXTRACT(EPOCH FROM (NOW() - lp.gps_time)) / 3600 AS hours_since_fix
|
||
|
|
FROM tracksolid.devices d
|
||
|
|
LEFT JOIN tracksolid.live_positions lp ON lp.imei = d.imei
|
||
|
|
WHERE d.enabled_flag = 1
|
||
|
|
AND (
|
||
|
|
lp.gps_time IS NULL
|
||
|
|
OR lp.gps_time < NOW() - INTERVAL '2 hours'
|
||
|
|
)
|
||
|
|
ORDER BY hours_since_fix DESC NULLS FIRST;
|