I have an Alert model that has an AlertState field, and an
AlertStateChange model that has fields for Alert, AlertState and a
timestamp. I'd like to know the best way to create a report that has
the average time between two given states. Currently, I have a custom
save method in alert that create a new AlertStateChange each time the
Alert's state field changes. When I want the report I do an SQL query
like this:
SELECT
AVG(times.time) AS average_time
FROM
(
SELECT
MAX(
extract(epoch from c2.timestamp)
- extract(epoch from c1.timestamp)
) AS time
FROM
alertdb_alert AS alert,
alertdb_alertstatechange AS c1,
alertdb_alertstatechange AS c2
WHERE
c1.alert_id = c2.alert_id
AND c1.state_id = %s
AND c2.state_id = %s
GROUP BY
c1.alert_id
ORDER BY
c1.alert_id
) AS times
The two %s's are the two states.
Is this be best way or is there a better way?
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Django users" group.
To post to this group, send email to django-users@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/django-users?hl=en
-~----------~----~----~----~------~----~------~--~---
