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
-~----------~----~----~----~------~----~------~--~---

Reply via email to