Django - 日期时间列之间的差异总和
我有记录first_action 和last_action 日期时间列的UserSession 模型， 我想知道用户在应用程序中花费了多长时间 - 这是两者之间的差异之和（忽略那些带有 Null last_action 的内容）。
不幸的是，django ORM 聚合函数仅适用于单个列，因此为了提高效率（每个用户可以有数千个会话），我用原始 SQL 编写了它，但因为我得到了一些很高的数字，所以我将其与 python 求和进行比较。不知道为什么，两次计算的结果不一样，你能帮我解决一下吗？
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;
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;
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
我发现了问题 - 我不知道在 mysql 中从一个日期时间减去另一个日期时间会做什么，但这不是我想要的。
正确的 SQL 查询是：
现在 sql 结果与 python 结果相同 - 无需将所有会话拉入内存。
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:
now the sql result is the same as the python result - without pulling all the sessions to memory.