mysql - 高级 SQL 日期减法

Question

我正在开发一款游戏，它可以跟踪您的登录时长。它会将签到时间以及 checkout 日期保存到数据库中。

这是一个游戏，类似于夺旗，只是我不希望它做的是计算 18:00(下午 6 点)和 09:00(上午 9 点)之间的时间和分数。这适用于每一天，而不仅仅是一次。

分数以秒为单位显示。简单地说，这是入住和退房时间之间的时差(每天减去 18:00-09:00 之间的时间)。

有人是高级 SQL 日期计算方面的专家，可以帮助我吗？请!

这是我的基本查询，没有减去时间:

SELECT
SUM(TIMESTAMPDIFF(SECOND,sessions.checkin,IFNULL(sessions.checkout,NOW()))) 
AS score
FROM `sessions`
GROUP BY `user_id`

Answer 1

它不会很漂亮(如果您要经常这样做，您可能需要添加 functions 以提高查询的可读性)

假设您没有跨越多天的登录，您可以执行以下操作

   SELECT
       user_id,
       SUM(
          TIMESTAMPDIFF(SECOND,
             CASE 
                WHEN sessions.checkin < timestamp(date(sessions.checkin), maketime(9,0,0))
                   THEN timestamp(date(sessions.checkin), maketime(9,0,0))
                WHEN sessions.checkin > timestamp(date(sessions.checkin), maketime(18,0,0))
                   THEN timestamp(date(sessions.checkin)+1, maketime(9,0,0))
                ELSE
                   sessions.checkin
             END,
             CASE
                WHEN IFNULL(sessions.checkout, now()) > timestamp(date(IFNULL(sessions.checkout, now())), maketime(18,0,0))
                   THEN timestamp(date(IFNULL(sessions.checkout, now())), maketime(18,0,0))
                WHEN IFNULL(sessions.checkout, now()) < timestamp(date(IFNULL(sessions.checkout, now())), maketime(9,0,0))
                   THEN timestamp(date(IFNULL(sessions.checkout, now()))-1, maketime(18,0,0))
                ELSE
                   IFNULL(sessions.checkout, now())
             END)) AS score
    FROM `sessions`
    GROUP BY `user_id`

如果登录跨越多天，或者更确切地说，查询可以处理跨越多天的登录，例如第一天 18:05 签到和第三天 8:55 checkout ，则它不起作用，但是作为一旦它跨越多天的可数时间，计算就不会结束 - 对于每条跨越多天可数时间的记录，您需要从结果中减去不可数秒数。如果您需要这方面的帮助，请告诉我。

上面的查询应该相对便宜地运行 - 考虑到您在整个表上运行它无论如何，所以它会进行表扫描，因为有很多重复计算而且它看起来很大应该无关紧要。这些计算在内存中进行，没有额外的 I/O，并且应该比磁盘 I/O 快几个数量级(与您的原始查询的执行时间比较，如果您发现性能下降，请告诉我们)。