PostgreSQL按天统计登录时长：跨天会话时长截断至当日

你的核心问题是跨天会话的时长被全部计入会话开始日期的统计，而你需要按实际日期拆分每个会话的贡献时长。原查询依赖表中的date字段和duration字段，但这两个字段无法处理跨天场景：date字段可能不是会话实际涉及的日期（比如你示例中的date是2023-04-30，和会话时间完全不符），duration是整个会话的时长，没有拆分跨天部分。

解决方案：按会话覆盖的日期拆分计算时长 #

我们可以用PostgreSQL的generate_series生成会话覆盖的所有日期，然后对每个日期计算该会话在当天的有效时长，最后按天求和。

最终SQL查询

SELECT
  date_trunc('day', day_date)::date AS date,
  -- 统计总时长（秒），如需小时可除以3600
  SUM(EXTRACT(EPOCH FROM (LEAST(end_time, day_date + INTERVAL '1 day') - GREATEST(start_time, day_date)))) AS time_spent_seconds
FROM user_activities,
     -- 生成会话覆盖的每一天的起始时间
     generate_series(
       date_trunc('day', start_time),
       date_trunc('day', end_time),
       INTERVAL '1 day'
     ) AS day_date
WHERE name = 'session'
  AND user_id = '#{user_id}'
GROUP BY date
ORDER BY date;

关键逻辑解释 #

1. 生成日期序列：generate_series会根据会话的start_time和end_time，生成该会话覆盖的所有日期的0点时间（比如跨天会话会生成两天的0点时间）。
2. 计算当日有效时长：
   • GREATEST(start_time, day_date)：取会话开始时间和当日0点的较大值，即会话在当日的实际开始时间。
   • LEAST(end_time, day_date + INTERVAL '1 day')：取会话结束时间和当日24点的较小值，即会话在当日的实际结束时间。
   • 两者相减得到该会话在当日的有效时长，用EXTRACT(EPOCH FROM ...)转换为秒数方便求和。
3. 按天分组统计：对每个日期的有效时长求和，得到单日的总登录时长。

示例验证 #

对于你提供的跨天会话：

• start_time=2023-04-24 23:35:56，end_time=2023-04-25 20:24:20
• generate_series会生成2023-04-24 00:00:00和2023-04-25 00:00:00两个日期
• 2023-04-24的有效时长：2023-04-25 00:00:00 - 2023-04-24 23:35:56 ≈ 24分钟
• 2023-04-25的有效时长：2023-04-25 20:24:20 - 2023-04-25 00:00:00 ≈ 20小时24分钟
• 两天的时长会分别计入对应日期的统计，不会出现单日时长超过24小时的问题。

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