如何合并两行并计算MySQL中两个时间戳值之间的时间差? [英] How to combine two rows and calculate the time difference between two timestamp values in MySQL?

问题描述

我有一种情况可以肯定是很普遍的，这让我感到困扰，因为我不知道该怎么做或找不到相关示例/解决方案的搜索内容.我对MySQL相对较新(较早使用MSSQL和PostgreSQL)，我能想到的每种方法都被MySQL缺少的某些功能所阻止.

I have a situation that I'm sure is quite common and it's really bothering me that I can't figure out how to do it or what to search for to find a relevant example/solution. I'm relatively new to MySQL (have been using MSSQL and PostgreSQL earlier) and every approach I can think of is blocked by some feature lacking in MySQL.

我有一个日志"表，该表仅列出了许多不同的事件及其时间戳(存储为datetime类型).表中有很多与此问题无关的数据和列，因此可以说我们有一个简单的表，如下所示:

I have a "log" table that simply lists many different events with their timestamp (stored as datetime type). There's lots of data and columns in the table not relevant to this problem, so lets say we have a simple table like this:

CREATE TABLE log (  
  id INT NOT NULL AUTO_INCREMENT,  
  name VARCHAR(16),  
  ts DATETIME NOT NULL,  
  eventtype VARCHAR(25),  
  PRIMARY KEY  (id)  
)

让我们说某些行的事件类型='开始'，而另一些行的事件类型='停止'.我想要做的是以某种方式将每个起始行"与每个停止行"耦合在一起，找出两者之间的时间差(然后将每个名称的持续时间相加，但这不是问题所在).每个开始"事件应该在开始"事件之后的某个阶段发生一个相应的停止"事件，但是由于数据收集器的问题/错误/崩溃，可能是某些事件丢失了.在这种情况下，我想在没有合作伙伴"的情况下无视这次活动.这意味着给定数据:

Let's say that some rows have an eventtype = 'start' and others have an eventtype = 'stop'. What I want to do is to somehow couple each "startrow" with each "stoprow" and find the time difference between the two (and then sum the durations per each name, but that's not where the problem lies). Each "start" event should have a corresponding "stop" event occuring at some stage later then the "start" event, but because of problems/bugs/crashed with the data collector it could be that some are missing. In that case I would like to disregard the event without a "partner". That means that given the data:

foo, 2010-06-10 19:45, start  
foo, 2010-06-10 19:47, start  
foo, 2010-06-10 20:13, stop

..我只想忽略19:45开始事件，而不仅仅是使用20:13停止事件作为停止时间来获取两个结果行.

..I would like to just disregard the 19:45 start event and not just get two result rows both using the 20:13 stop event as the stop time.

我试图以不同的方式将表与自身连接，但是对我来说，关键的问题似乎是找到一种方法，以给定的名称"正确地将对应的停止"事件标识为开始"事件. ".问题与您有一张桌子摆放着员工进出办公室并想弄清楚他们实际在工作中有多少工作时完全一样.我确定必须有众所周知的解决方案，但我似乎找不到它们...

I've tried to join the table with itself in different ways, but the key problems for me seems to be to find a way to correctly identify the corresponding "stop" event to the "start" event for the given "name". The problem is exactly the same as you would have if you had table with employees stamping in and out of work and wanted to find out how much they actually were at work. I'm sure there must be well known solutions to this, but I can't seem to find them...

推荐答案

我相信这可能是实现目标的一种更简单的方法:

I believe this could be a simpler way to reach your goal:

SELECT
    start_log.name,
    MAX(start_log.ts) AS start_time,
    end_log.ts AS end_time,
    TIMEDIFF(MAX(start_log.ts), end_log.ts)
FROM
    log AS start_log
INNER JOIN
    log AS end_log ON (
            start_log.name = end_log.name
        AND
            end_log.ts > start_log.ts)
WHERE start_log.eventtype = 'start'
AND end_log.eventtype = 'stop'
GROUP BY start_log.name

由于它消除了一个子查询，因此它应该运行得更快.

It should run considerably faster as it eliminates one subquery.

这篇关于如何合并两行并计算MySQL中两个时间戳值之间的时间差?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！