如何在MySQL/SQLAlchemy中计算实体事件的时长？

The Event Model #

First, here's the SQLAlchemy model you're using to track entity events:

from datetime import datetime
from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()

class Event(db.Model):
    __tablename__ = 'event'
    id = db.Column(db.Integer, primary_key=True)
    entity_id = db.Column(db.Integer, db.ForeignKey('entity.id'))
    mode = db.Column(db.Integer)
    timestamp = db.Column(db.DateTime, default=datetime.datetime.utcnow)
    duration = db.Column(db.Integer, nullable=False, default=0, server_default=db.text('0'))

Key Scenario & Logic #

From your description, here's how the event lifecycle works and how to handle duration updates:

• When an event starts, you create a new Event record with duration set to 0 (since we don't know the end time yet).
• When the same entity switches to a different mode, you need to retroactively calculate and update the previous event's duration to reflect how long it ran.

Implementation Steps #

Here's a practical approach to handle this flow:

1. Before creating a new event:
   • Fetch the most recent event for the target entity using entity_id, sorted by timestamp descending.
   • If that event exists and uses a different mode than the new one:
     • Calculate the duration as the time difference between the current UTC time and the previous event's timestamp (convert to seconds or your preferred unit).
     • Update the previous event's duration field and commit the change.
2. Create the new event:
   • Instantiate a new Event with the current mode, default timestamp, and duration 0, then commit it to the database.

Example Code Snippet #

def create_new_event(entity_id, new_mode):
    # Retrieve the latest event for the entity
    last_event = Event.query.filter_by(entity_id=entity_id).order_by(Event.timestamp.desc()).first()
    
    if last_event and last_event.mode != new_mode:
        # Calculate duration in seconds (adjust unit as needed)
        duration = int((datetime.utcnow() - last_event.timestamp).total_seconds())
        last_event.duration = duration
        db.session.commit()
    
    # Create and save the new event
    new_event = Event(entity_id=entity_id, mode=new_mode)
    db.session.add(new_event)
    db.session.commit()
    return new_event

Edge Cases to Consider #

• If there are no previous events for the entity, the first event will simply be created with duration 0 (no updates needed).
• If your app uses timezone-aware timestamps, ensure all datetime operations use timezone-aware objects to avoid calculation errors.

内容的提问来源于stack exchange，提问作者Roman Susi