The relates to OpenNMS where I'm trying to write an automation in SQL. I have a system which sends events to a table every time the backup succeeds. I want to detect if the backup is overdue for any given node. So for example the (simplified) table looks like:
nodeid, eventid, eventuei, eventtime
1 , 1 , backupOk, 09:20 15/12/09
2 , 2 , backupOk, 09:25 15/12/09
3 , 3 , backupOk, 09:30 15/12/09
1 , 4 , backupOk, 09:20 16/12/09
2 , 5 , backupOk, 09:25 16/12/09
2 , 6 , backupOk, 09:25 17/12/09
3 , 7 , backupOk, 09:30 17/12/09
So what I need is a list of nodeid's where the backup is overdue by 24 hours (and I guess where no backup has occurred at all, although there are nodes in this database that don't get backed up (as they are different types of node)).
Getting list of nodes that were not backed up - from your schema that you showed us - is not possible.
Getting list of nodes that are 24 hours overdue is trivial:
select nodeid, max(eventtime)
from your_table
group by nodeid
having max(eventtime) < now() - '24 hours'::interval