How can I use DATEDIFF to find the difference in minutes between rows?

Question

I found a variety of previous topics that addressed this such as:

SQL Datediff - find datediff between rows SQL query to get DateDiff of the last two records

However, I cannot understand how this works. I am not advanced or an expert in SQL, so this is bothering me. I have created the following on fiddle:

CREATE TABLE activity
(
  IDOrder int,
  IDActivity int,
  DateActivity DateTime
);

INSERT INTO activity (IDOrder, IDActivity, DateActivity) VALUES 
(1, 1, "2007-04-16 08:34:00"),
(1, 2, "2007-04-16 09:22:00"), 
(1, 3, "2007-04-16 09:51:00"),
(1, 4, "2007-04-16 16:14:00"), 
(2, 1, "2007-04-16 08:34:00"),
(3, 1, "2007-04-16 08:34:00"), 
(3, 2, "2007-04-16 09:22:00"),
(3, 3, "2007-04-16 09:51:00"), 
(3, 4, "2007-04-16 16:14:00"),
(4, 1, "2007-04-16 08:34:00"), 
(4, 2, "2007-04-16 09:22:00"),
(4, 3, "2007-04-16 09:51:00");

INSERT INTO activity (IDOrder, IDActivity , DateActivity) VALUES 
(2, 2, CURRENT_TIMESTAMP), 
(2, 3, CURRENT_TIMESTAMP);

Could someone give me a push in the right direction? What I want as output is 4 columns for these differences in minutes. Time 1 is the difference between IDActivity 1 and 2. Time 2 is the difference between IDActivity 2 and 3. Time 3 is between 3 and 4 and Time 4 is between 4 and 1. In some instances where the IDActivity doesn't exist, I would want it to return null.

The best idea I have is using an IF THEN statement, a giant loop returning 4 columns of the data, but am unsure how to implement it.

Add expected Output which will be really helpful to understand your question — Pரதீப், Nov 20 '14 at 03:50
Sorry, I wasn't sure if the second to last paragraph was clear enough. Each IDOrder is associated with a given IDActivity, and DateActivity. What I wanted to do was for each order (there are 4 of em), I wanted to create 4 columns; the difference between IDActivity 1 and IDActivity 2 for IDOrder 1 would be Time Column 1. Do this for 3 columns, aka IDActivity 2/3, IDActivity 3/4, IDActivity 4/1. Then I'd have to do it for each order, as there are 4 orders, but they may not necessarily have IDActivity up to 4 (in which case it would return a null...ideally). Does that clarify? Thanks again. — Yam Y, Nov 20 '14 at 03:56
which database are you coding this for? You have both MySQL and sql-server tagged. — Kevin Cook, Nov 20 '14 at 13:43

score 0 · Answer 1 · answered Nov 20 '14 at 04:25

Try this one :

column "timeMinute" is difference in minutes between IDActivity no x and no x+1 in the same "IDOrder".

Select a.*, datediff(minute, a.DateActivity, b.DateActivity) as timeMinute
From Activity a
Left Join Activity b
    On b.IDOrder = a.IDOrder
    And b.IDActivity-1 = a.IDActivity
Order By a.IDOrder, a.IDActivity

AdamMc331 · Accepted Answer · 2014-11-20T04:51:31.727

You were on the right track by considering case statements. I will walk you through this the same way I solved it.

I started by writing a select query that used a case statement to get the first, second, third, and fourth activity dates:

SELECT idorder, 
   (CASE WHEN idactivity = 1 THEN dateactivity ELSE NULL END) AS firstDate,
   (CASE WHEN idactivity = 2 THEN dateactivity ELSE NULL END) AS secondDate,
   (CASE WHEN idactivity = 3 THEN dateactivity ELSE NULL END) AS thirdDate,
   (CASE WHEN idactivity = 4 THEN dateactivity ELSE NULL END) AS fourthDate
FROM activity;

However, this returns an ugly result set of four rows for each group. The first row has a firstDate value, and null for the rest. The second row has a secondDate value, and null for first, third, and fourth date. See the SQL Fiddle further down for an image.

So, I used this as a subquery and used the MAX() function to get each of the dates and have one row per idorder value:

SELECT idorder,
  MAX(firstDate) AS firstDate,
  MAX(secondDate) AS secondDate,
  MAX(thirdDate) AS thirdDate,
  MAX(fourthDate) AS fourthDate
FROM(SELECT idorder, 
     (CASE WHEN idactivity = 1 THEN dateactivity ELSE NULL END) AS firstDate,
     (CASE WHEN idactivity = 2 THEN dateactivity ELSE NULL END) AS secondDate,
     (CASE WHEN idactivity = 3 THEN dateactivity ELSE NULL END) AS thirdDate,
     (CASE WHEN idactivity = 4 THEN dateactivity ELSE NULL END) AS fourthDate
      FROM activity) t
GROUP BY idorder;

Then, I was able to modify the above query to use the TIMESTAMPDIFF() function in MySQL to get the difference in minutes. You still need to calculate the MAX however, and just compare the max of first and second, and the max of third and fourth. It looks like this:

SELECT idorder,
  TIMESTAMPDIFF(minute, MAX(firstDate), MAX(secondDate)) AS firstTimeDifference,
  TIMESTAMPDIFF(minute, MAX(thirdDate), MAX(fourthDate)) AS secondTimeDifference
FROM(SELECT idorder, 
     (CASE WHEN idactivity = 1 THEN dateactivity ELSE NULL END) AS firstDate,
     (CASE WHEN idactivity = 2 THEN dateactivity ELSE NULL END) AS secondDate,
     (CASE WHEN idactivity = 3 THEN dateactivity ELSE NULL END) AS thirdDate,
     (CASE WHEN idactivity = 4 THEN dateactivity ELSE NULL END) AS fourthDate
      FROM activity) t
GROUP BY idorder;

Here is the SQL Fiddle.

EDIT

The above does not return all four columns you need, but hopefully you have the skills you need to get the other values.

This makes an unbelievable amount of sense. And yes, I was able to get the other value columns by modifying the select statement and adding in the other two columns for the time differences accordingly! I didn't know that there were THIS many time usages; I spent more time thinking of how to use datediff correctly (based on year, minutes, date) that I was trying to convert it to minutes first. Bah. Thanks! — Yam Y, Nov 20 '14 at 14:05
@YamY Bookmark this [link.](http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html) I use it all the time when I need to manipulate datetime columns. — AdamMc331, Nov 20 '14 at 17:13
@YamY also note that those functions are for MySQL. You tagged both that and sql-server and I'm not sure which you are doing, but there will be some similarities. Regardless of syntax, the logic behind solving this query should be the same. — AdamMc331, Nov 20 '14 at 17:14

How can I use DATEDIFF to find the difference in minutes between rows?

2 Answers2