同一列中日期之间的Mysql时间不同

Question

对于特定的ticket_id，我想采用stalled(NewValue)和stalled(OldValue)之间的“创建”时间差

注:这是我的第一篇帖子，由于一些限制，我不能附上照片。

+-----------+----------+----------+---------------------+
| Ticket_id | OldValue | NewValue | Created             |
+-----------+----------+----------+---------------------+
| 20100     | new      | wip      | 2012-10-01 07:38:18 |
| 20100     | wip      | stalled  | 2012-10-01 10:12:14 |
| 20100     | stalled  | wip      | 2012-10-01 22:09:39 |
| 20100     | wip      | open     | 2012-10-02 01:03:26 |
| 20100     | open     | stalled  | 2012-10-02 01:03:26 |
| 20100     | stalled  | wip      | 2012-10-03 06:55:42 |
| 20100     | wip      | open     | 2012-10-03 19:24:53 |
| 20100     | open     | stalled  | 2012-10-03 19:24:53 |
| 20100     | stalled  | open     | 2012-10-04 19:28:48 |
| 20100     | open     | resolved | 2012-10-04 21:01:17 |
+-----------+----------+----------+---------------------+

Answer 1

使用像这样的东西：

SET @dte:=CURRENT_DATE();
SELECT DATEDIFF(Created, @dte),@dte:=Created FROM table ORDER BY Created

这将显示两个值之间的差异(第一条记录除外)。你可以添加一个额外的如果或只是不显示第一次约会。

Answer 2

这是我找到的一个解决方案。我不确定你是否在使用JIRA，但我确实是(看起来你可能是)。我需要找出特定JIRA票证保持特定状态的时间(尽管它已经更改了多次)。

SELECT
  ThisLog.issueid,
  b.NEWSTRING,
  SUM(TIMESTAMPDIFF(HOUR,ThisLog.created,PrevLog.created)*-1) AS "Status Time"
FROM
  changegroup    AS ThisLog
LEFT JOIN
  changegroup    AS PrevLog
    ON  PrevLog.issueid = ThisLog.issueid
    AND PrevLog.created = (SELECT MAX(created)
                          FROM changegroup
                          LEFT JOIN changeitem AS b1
                          ON changegroup.ID = b1.groupid
                          WHERE issueid = ThisLog.issueid
                          AND created < ThisLog.created
                          AND b1.field = "status"
                          AND b1.NEWSTRING = "Pending Customer")
LEFT JOIN changeitem AS b
ON ThisLog.ID = b.groupid
WHERE ThisLog.issueid = 82720 AND b.field = "status" AND b.NEWSTRING = "Pending Customer";