CODEWITHSUNDEEP
sqldjangoormaggregate-functions
Iftah
Iftah

Reputation: 9572

Django - Aggregate sum of difference between datetime column

I have UserSession models that record first_action and last_action datetime columns, I want to know how long a user spent in the app - that is the sum of the difference between the two (ignoring those with Null last_action).

Unfortunately django ORM aggregate functions only work on a single column, so for the sake of efficiency (there can be thousands of sessions per user) I wrote it in raw SQL, but because I got some high numbers I compared to a python sum. I don't know why, but the two calculations do not show the same result, can you help me figure it out?

Python:

sum(((us.last_action - us.first_action) for us in UserSession.objects.filter(user_id=1234) if us.last_action), datetime.timedelta(0))

SQL:

select sum(last_action - first_action) as total_time   from usersession where user_id = 1234 and last_action is not null;

Upvotes: 1

Views: 1599

Answers (1)

Iftah
Iftah

Reputation: 9572

I found the problem - I don't know what subtracting one datetime from another does in mysql, but it wasn't what I wanted.

The correct SQL query is:

select sum(timestampdiff(SECOND, first_action, last_action )) as total_time  from usersession where user_id = 123 and last_action is not null

now the sql result is the same as the python result - without pulling all the sessions to memory.

Upvotes: 1

Related Questions