两行之间的MySQL日期差异。

I have a TABLE with Columns: USER_ID,TIMESTAMP and ACTION

我有一个包含列的表:USER_ID、时间戳和操作。

Every row tells me which user did what action at a certain time-stamp.

每一行告诉我哪个用户在某个时间戳中做了什么动作。

Example:

例子:

Alice starts the application at 2014-06-12 16:37:46
Alice于2014-06-12 16:37:46开始申请
Alice stops the application at 2014-06-12 17:48:55
Alice在2014-06-12 17:48:55停止申请

I want a list of users with the time difference between the first row in which they start the application and the last row in which they close it.

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

Here is how I'm trying to do it:

以下是我要做的:

    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";

I ask for the DATEDIFF between two SELECT queries, but I just get a list of Alice`s with -2 as Duration.

我在两个SELECT查询之间请求DATEDIFF，但我只得到一个Alice的列表，其中-2是持续时间。

Am i on the right track?

我走对方向了吗?

3 个解决方案

#1

I think you should group this table by USER_ID and find minimum date of "START_APP" and maximum of "CLOSE_APP" for each user. Also you should use in DATEDIFF the CLOSE_APP time first and then START_APP time in this case you will get positive value result

我认为您应该根据USER_ID对这个表进行分组，并为每个用户查找“START_APP”的最小日期和“CLOSE_APP”的最大值。你还应该先在DATEDIFF中使用CLOSE_APP time然后在这个例子中使用START_APP time你会得到正值的结果

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

SQLFiddle演示

#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";

You make two "tables" with the earliest time for start for each user, and the latest time for close for each user. Then join them so that the actions of the same user are together.

您创建了两个“表”，每个用户的启动时间最早，每个用户的关闭时间最晚。然后将它们连接在一起，以便同一用户的操作在一起。

Now that you have everything setup you can easily subtract between them.

现在你已经有了所有的设置，你可以很容易地减去它们。

#3

You have the int value because you use the function DATEDIFF, it shows you the number of days between two dates, if you want to have the number of hours and minutes and seconds between dates you have to use TIMEDIFF Try this:

你有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

It will show you difference between two latest dates (startdate,enddate) for all user.

它将显示所有用户的两个最新日期(startdate,enddate)之间的差异。

P.S: Sorry, I wrote it without any databases, and may be there are some mistakes. If you have problems with (t1.action,t1.timestamp) in (select...) split it on two: where t1.action in (select ...) and t1.timestamp in (select ...)

P。S:对不起，我是在没有数据库的情况下写的，可能有错误。如果(t1.action,t1.timestamp)在(select…)中有问题，请将它分割为两个:where t1。操作(select…)和t1。时间戳(选择…)

#1

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