Hi Pete,
Subtracting the begin_time from end_time will give you a time interval.
The only tricky part is that the end_time for an ongoing event is
'infinity', which can't be subtracted from. You can use an SQL CASE
statement to get around that. It would look roughly like this:
SELECT map_id, device_id, status, reason, begin_time,
CASE WHEN end_time = 'infinity' THEN interval '99 days'
ELSE end_time - begin_time
END
FROM event
WHERE status != 'okay'
AND age(begin_time) < interval '24 hours'
ORDER BY begin_time ASC
Events that are still ongoing get a duration of '99 days'; you could
make that zero, or whatever else makes sense. Or convert the end_time -
begin_time to text, so you can return the word 'ongoing' for the
infinity case.
You could join in the map/device/interface tables to create a query that
returns the device's name, IP address, or whatever other information
you're interested in. With just the event table, you could select
server_id, map_id, device_id, and use those with IM's 'Find Devices', by
searching for, i.e. 'device.1.5.3'. Hope that helps,
David
Peter Milburn (MIPE) wrote:
I am kind of new to postgres. I am writing a simple php page to display
events that have happened over the last 24 hours. I am trying to count
the time between the start of a event and the finish of the event from
the event table.
--
David Schnur
Dartware, LLC
http://www.dartware.com
____________________________________________________________________
List archives:
http://www.mail-archive.com/intermapper-talk%40list.dartware.com/
To unsubscribe: send email to: [EMAIL PROTECTED]
