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