时间戳

首页 > 解决方案 > 计算每个用户登录之间的时间

mysql - 计算每个用户登录之间的时间

问题描述

我有一个包含唯一 ID、开始时间、结束时间和用户 ID 的表，我们将此表称为 User_Logged_In，其中开始时间是用户登录时，结束时间是用户注销时。

我正在尝试显示最近 5 次计算的“自上次活动以来的时间”时间，以天/小时/分钟/秒为单位，按单个用户（用户 ID）分组，并从最近到最旧排序。

用户 A 可能有 3 个登录，用户 B 可能有 12 个登录，用户 C 可能有 8 个登录。对于每个用户，我正在寻找每次登录之间的离线时间。按用户排序，然后开始时间 DESC，我希望每个用户的最新记录没有值。

例子：

+------+---------------------+---------------------+---------+
|  id  | starttime           | endtime             | user_id |
+------+---------------------+---------------------+---------+
|  35  | 2018-12-19 15:20:45 | 2018-12-19 19:21:06 | 1       |
|  22  | 2018-12-19 14:51:39 | 2018-12-19 21:56:40 | 1       |
|  11  | 2018-01-05 14:28:17 | 2018-01-05 21:30:42 | 1       |
|  97  | 2019-09-25 13:17:34 | 2019-09-25 23:31:31 | 2       |
|  86  | 2019-07-31 20:42:16 | 2019-07-31 22:42:38 | 2       |
|  72  | 2019-05-21 17:49:21 | 2019-05-21 20:03:03 | 2       |
|  83  | 2019-07-31 14:30:39 | 2019-07-31 20:30:58 | 3       |
|  79  | 2019-07-30 13:49:23 | 2019-07-30 23:59:01 | 3       |
|  61  | 2019-04-12 15:03:25 | 2019-04-13 01:54:23 | 3       |
|  54  | 2019-02-03 06:45:33 | 2019-02-03 17:33:48 | 3       |
|  51  | 2019-01-23 09:12:15 | 2019-01-29 14:44:38 | 3       |
+------+---------------------+---------------------+---------+

http://sqlfiddle.com/#!9/0dba46/2

最终结果：

+----+---------------------+---------------------+-------------------------------------------+---------+
| id | starttime           | endtime             | difference                                | user_id |
+----+---------------------+---------------------+-------------------------------------------+---------+
| 35 | 2018-12-19 15:20:45 | 2018-12-19 19:21:06 | -                                         | 1       |
+----+---------------------+---------------------+-------------------------------------------+---------+
| 22 | 2018-12-19 14:51:39 | 2018-12-19 21:56:40 | 0 Days, 6 Hours, 35 Minutes, 55 Seconds   | 1       |
+----+---------------------+---------------------+-------------------------------------------+---------+
| 11 | 2018-01-05 14:28:17 | 2018-01-05 21:30:42 | 347 Days, 17 Hours, 57 Minutes, 23 Seconds | 1       |
+----+---------------------+---------------------+-------------------------------------------+---------+
| 97 | 2019-09-25 13:17:34 | 2019-09-25 23:31:31 | -                                         | 2       |
+----+---------------------+---------------------+-------------------------------------------+---------+
| 86 | 2019-07-31 20:42:16 | 2019-07-31 22:42:38 | 55 Days, 14 Hours, 56 Minutes, 15 Seconds  | 2       |
+----+---------------------+---------------------+-------------------------------------------+---------+
| 72 | 2019-05-21 17:49:21 | 2019-05-21 20:03:03 | 71 Days, 0 Hours, 39 Minutes, 13 Seconds  | 2       |
+----+---------------------+---------------------+-------------------------------------------+---------+
| 83 | 2019-07-31 14:30:39 | 2019-07-31 20:30:58 | -                                         | 3       |
+----+---------------------+---------------------+-------------------------------------------+---------+
| 79 | 2019-07-30 13:49:23 | 2019-07-30 23:59:01 | 1 Days, 6 Hours, 41 Minutes, 35 Seconds   | 3       |
+----+---------------------+---------------------+-------------------------------------------+---------+
| 61 | 2019-04-12 15:03:25 | 2019-04-13 01:54:23 | 108 Days, 11 Hours, 55 Minutes, 0 Seconds | 3       |
+----+---------------------+---------------------+-------------------------------------------+---------+
| 54 | 2019-02-03 06:45:33 | 2019-02-03 17:33:48 | 67 Days, 21 Hours, 29 Minutes, 37 Seconds  | 3       |
+----+---------------------+---------------------+-------------------------------------------+---------+
| 51 | 2019-01-29 09:12:15 | 2019-01-23 14:44:38 | 10 Days, 16 Hours, 0 Minutes, 55 Seconds   | 3       |
+----+---------------------+---------------------+-------------------------------------------+---------+

该值将是更大数据集中的一列。我尝试了许多不同的方法，包括重新加入同一张表，但找不到快速返回此值的方法，或者根本找不到。

目前在 MySQL v5.6 上。

标签： mysqlquery-optimization

解决方案

从您的 SQLFiddle 链接中获取代码后，我创建了一个似乎需要的输出。这一切都依赖于函数TIMESTAMPDIFF，SEC_TO_TIME而且TIME_FORMAT，您可能不需要所有结果列，我选择了以秒为单位的差异，只是为了获得所需输出的中间结果。
以下查询可能满足要求：

SELECT
  user_id AS User,
  starttime AS Start,
  endtime AS End,
  TIMESTAMPDIFF(SECOND, starttime, endtime) AS "Difference in seconds",
  CONCAT(
    FLOOR(TIME_FORMAT(SEC_TO_TIME(TIMESTAMPDIFF(SECOND, starttime, endtime)), '%H') / 24), ' days ',
      MOD(
        TIME_FORMAT(SEC_TO_TIME(TIMESTAMPDIFF(SECOND, starttime, endtime)), '%H'), 24), ' hours, ',
        TIME_FORMAT(SEC_TO_TIME(TIMESTAMPDIFF(SECOND, starttime, endtime)), '%i minutes and %s seconds'
      )
   ) AS "readable Difference"
FROM
  Table1

这将在提到的小提琴链接中输出以下内容：

User    Start                   End                     Difference in seconds   readable Difference
1       2018-12-19T15:20:45Z    2018-12-19T19:21:06Z    14421                   0 days 4 hours, 00 minutes and 21 seconds
1       2018-12-19T14:51:39Z    2018-12-19T21:56:40Z    25501                   0 days 7 hours, 05 minutes and 01 seconds
1       2018-01-05T14:28:17Z    2018-01-05T21:30:42Z    25345                   0 days 7 hours, 02 minutes and 25 seconds
2       2019-09-25T13:17:34Z    2019-09-25T23:31:31Z    36837                   0 days 10 hours, 13 minutes and 57 seconds
2       2019-07-31T20:42:16Z    2019-07-31T22:42:38Z    7222                    0 days 2 hours, 00 minutes and 22 seconds
2       2019-05-21T17:49:21Z    2019-05-21T20:03:03Z    8022                    0 days 2 hours, 13 minutes and 42 seconds
3       2019-07-31T14:30:39Z    2019-07-31T20:30:58Z    21619                   0 days 6 hours, 00 minutes and 19 seconds
3       2019-07-30T13:49:23Z    2019-07-30T23:59:01Z    36578                   0 days 10 hours, 09 minutes and 38 seconds
3       2019-04-12T15:03:25Z    2019-04-13T01:54:23Z    39058                   0 days 10 hours, 50 minutes and 58 seconds
3       2019-02-03T06:45:33Z    2019-02-03T17:33:48Z    38895                   0 days 10 hours, 48 minutes and 15 seconds
3       2019-01-29T09:12:15Z    2019-01-23T14:44:38Z    -498457                 -6 days -18 hours, -27 minutes and 37 seconds

如您所见，由于数据无效，结果的最后一行似乎不受欢迎。

推荐阅读