两行之间的MySQL日期差异

Question

我有一个TABLE，其中包含列：USER_ID、TIMESTAMP和ACTION

每一行告诉我哪个用户在特定的时间戳执行了什么操作。

例子：

• Alice 在 2014-06-12 16:37:46 启动应用程序
• Alice 在 2014-06-12 17:48:55 停止应用程序

我想要一个用户列表，其中包含他们启动应用程序的第一行和关闭它的最后一行之间的时间差。

以下是我的尝试方法:

    SELECT USER_ID,DATEDIFF(
(SELECT timestamp FROM MOBILE_LOG WHERE ACTION="START_APP" AND USER_ID="Alice"  order by TIMESTAMP LIMIT 1),
(SELECT timestamp FROM MOBILE_LOG WHERE ACTION ="CLOSE_APP" AND USER_ID="Alice"  order by TIMESTAMP LIMIT 1)
) AS Duration FROM MOBILE_LOG AS t WHERE USER_ID="Alice";

我要求两个SELECT查询之间的DATEDIFF，但我只得到了一个带有-2持续时间的Alice名单。

我是否在正确的轨道上？

- Mark

---

你想以毫秒还是小时的形式查看差异？如果您有一个掩码（提供它，例如：YYYY-MM-DD HH:MI:SS），请告诉我。 - neshkeev

一个口罩会很好！DD HH:MM（40天，3小时和4分钟） - Mark

Answer 1

我认为您应该按USER_ID分组该表，并找到每个用户的“START_APP”最小日期和“CLOSE_APP”的最大日期。另外，您应该在DATEDIFF 中先使用CLOSE_APP时间，然后使用START_APP时间，在这种情况下您将获得正值结果。

SELECT USER_ID,
       DATEDIFF(MAX(CASE WHEN ACTION="CLOSE_APP" THEN timestamp END),
                MIN(CASE WHEN ACTION="START_APP" THEN timestamp END)
               ) AS Duration 
FROM MOBILE_LOG AS t 
GROUP BY USER_ID

SQLFiddle demo

- valex

---

那行不通，开始和结束在不同的行。 - Ariel

我收回之前的话。它会起作用的 - 我完全忽略了末尾的 GROUP BY。非常聪明！感谢你教给我新东西，我点了个赞。然而 - 这种方法会非常慢，因为你无法对任何内容进行索引，所以实际上不要使用这种方法，还是使用我的方法吧。 - Ariel

这似乎是有效的，我只是想尝试删除新创建的Duration列上的NULL值，但简单的WHERE语句不起作用:( - Mark

@Mark 使用 HAVING 而不是 WHERE，因为条件发生在 GROUP BY 之后。但我想建议您使用我的代码，因为它运行速度更快（除非行数不多且速度不重要）。 - Ariel

@Ariel 你的两个解决方案都在0.06秒内顺利运行（我有一个小型数据库），我真的不知道该接受哪个答案 :-) - Mark

Answer 2

SELECT user_id, start_time, close_time, DATEDIFF(close_time, start_time) duration
FROM
   (SELECT MIN(timestamp) start_time, user_id FROM MOBILE_LOG WHERE action="START_APP" GROUP BY user_id) start_action
  JOIN
   (SELECT MAX(timestamp) close_time, user_id FROM MOBILE_LOG WHERE ACTION ="CLOSE_APP" GROUP BY user_id) close_action
  USING (user_id)
WHERE USER_ID="Alice";

您需要制作两张表格，一张记录每个用户的最早开始时间，另一张记录每个用户的最晚结束时间。然后将它们连接起来，以便同一用户的操作在一起。

现在您已经设置好了所有内容，可以轻松地进行相减操作。

- Ariel

Answer 3

您拥有 int 值是因为您使用了 DATEDIFF 函数，它显示两个日期之间的天数。如果您想要获取日期之间的小时、分钟和秒数，您需要使用 TIMEDIFF。请尝试以下代码：

select t1.USER_ID, TIMEDIFF(t2.timestamp, t1.timestamp)
  from MOBILE_LOG  t1, MOBILE_LOG  t2
 where (t1.action,t1.timestamp) in (select action, max(timestamp) from MOBILE_LOG t where t.ACTION = "START_APP" group by USER_ID)
   and (t1.action,t1.timestamp) in (select action, max(timestamp), max(id) from MOBILE_LOG t where t.ACTION = "CLOSE_APP" group by USER_ID)
   and t1.USER_ID = t2.USER_ID

它将显示所有用户的两个最新日期（startdate，enddate）之间的差异。

附言：抱歉，我没有使用任何数据库来编写它，可能会有一些错误。如果您在使用(t1.action,t1.timestamp) in (select...)时遇到问题，请将其拆分为两个部分：where t1.action in (select ...) and t1.timestamp in (select ...)

- neshkeev

---

那不是他问的。你应该自删你的回答。 - Ariel

他评论说他想看到小时和分钟的差异，是吗？ - neshkeev

下一次，请在评论中发布（前提是你有足够的积分）。你的回答中没有任何代码，只有指向其他地方的链接，stackoverflow不喜欢这样的回答。 - Ariel

抱歉，我不是SQL查询方面的专家，但您的所有帖子对我来说都很有趣。我正在考虑使用TIMEDIFF。 - Mark

修改了我的帖子，第一次读到您的问题时没有理解您需要什么。 - neshkeev