Difference in seconds between two times in two rows in the same column - MySql

Question

how to make the following

Table bestellungen

id      abholdatum         abholzeit

1       2014-02-03         03:35:00
2       2014-02-03         08:30:00
3       2014-02-03         05:10:00
4       2014-02-03         15:25:00
5       2014-02-03         11:50:00

I want this result

id      abholdatum         abholzeit endzeit       diff

1       2014-02-03         03:35:00  05:10:00      5700
3       2014-02-03         05:10:00  08:30:00      12000
2       2014-02-03         08:30:00  11:50:00      12000
5       2014-02-03         11:50:00  15:25:00      12900
4       2014-02-03         15:25:00  00:00:00      0

can someone give me an idea how to solve this

thx

Do you want all of the combination, or are you using some other logic to decide which to compare? Wait, I see now, they are being compared with the next one in time. — Damien Black, Feb 02 '14 at 04:59
Du solltest besser englische Namen benutzen, sonst bricht sich jeder den Hals ab. ;) And where is the endzeit column, in bestellungen? Oh, i see, tricky. ;) — loveNoHate, Feb 02 '14 at 05:00

score 1 · Accepted Answer · answered Feb 02 '14 at 05:40

First, you need to make pivot table as follows.

You can test here http://www.sqlfiddle.com/#!2/d62d4/3

SELECT x.id, x.abholdatum, x.abholzeit AS from_ts, y.abholzeit AS to_ts
FROM (
    SELECT @seq := @seq + 1 AS ord, id, abholdatum, abholzeit
    FROM tbl, (SELECT @seq := 0) init
    ORDER BY abholzeit
) x LEFT JOIN  (

    SELECT @seq2 := @seq2 + 1 AS ord, id, abholdatum, abholzeit
    FROM tbl, (SELECT @seq2 := 0) init
    ORDER BY abholzeit
) y ON x.ord = y.ord - 1;
+------+------------+----------+----------+
| id   | abholdatum | from_ts  | to_ts    |
+------+------------+----------+----------+
|    1 | 2014-02-03 | 03:35:00 | 05:10:00 |
|    3 | 2014-02-03 | 05:10:00 | 08:30:00 |
|    2 | 2014-02-03 | 08:30:00 | 11:50:00 |
|    5 | 2014-02-03 | 11:50:00 | 15:25:00 |
|    4 | 2014-02-03 | 15:25:00 | NULL     |
+------+------------+----------+----------+
5 rows in set (0.00 sec)

Second, Let's calculate difference of time fields.

SELECT x.id, x.abholdatum, x.abholzeit AS from_ts, y.abholzeit AS to_ts,
TO_SECONDS(CONCAT(x.abholdatum, ' ', y.abholzeit)) - TO_SECONDS(CONCAT(x.abholdatum, ' ', x.abholzeit)) AS diff_ts
FROM (
    SELECT @seq := @seq + 1 AS ord, id, abholdatum, abholzeit
    FROM tbl, (SELECT @seq := 0) init
    ORDER BY abholzeit
) x LEFT JOIN  (

    SELECT @seq2 := @seq2 + 1 AS ord, id, abholdatum, abholzeit
    FROM tbl, (SELECT @seq2 := 0) init
    ORDER BY abholzeit
) y ON x.ord = y.ord - 1;
+------+------------+----------+----------+---------+
| id   | abholdatum | from_ts  | to_ts    | diff_ts |
+------+------------+----------+----------+---------+
|    1 | 2014-02-03 | 03:35:00 | 05:10:00 |    5700 |
|    3 | 2014-02-03 | 05:10:00 | 08:30:00 |   12000 |
|    2 | 2014-02-03 | 08:30:00 | 11:50:00 |   12000 |
|    5 | 2014-02-03 | 11:50:00 | 15:25:00 |   12900 |
|    4 | 2014-02-03 | 15:25:00 | NULL     |    NULL |
+------+------------+----------+----------+---------+
5 rows in set (0.00 sec)

Damien Black · Answer 2 · 2014-02-02T05:35:53.933

Here is the code if all of your rows are on the same date, like your example:

SELECT id, abholdatum, abholzeit, MIN(endzeit) AS endzeit, TIMESTAMPDIFF(SECOND, MIN(endzeit), abholzeit) AS diff
   FROM bestellungen AS early
LEFT JOIN (
    SELECT abholzeit AS endzeit FROM bestellungen
) AS later
WHERE endzeit > abholzeit
GROUP BY id
ORDER BY abholzeit

What I am going is joining all combinations of anything in the table, but limiting it to situation where the second time (endzeit) is greater than the first (abholzeit). Then I am grouping by id so that there is only one entry for each start time.

Then I use MIN(endzeit) to get the lowest value of the end time (which had to be greater than the start time.) I then use the function TIMESTAMPDIFF to get the difference in seconds. Not quite sure how the last time will turn out.

Right now it'll break with results on different dates, but your example had all of one date. You need to do some work with different dates, since there are a lot of different combos where the date is earlier but the time is later, etc.

Different dates would be a lot easier if the date and time were in one column, then you could just use the above solution. Here is a (not fully functional) example of what it might look like for different dates:

SELECT id, abholdatum, abholzeit, MIN(endzeit) AS endzeit, TIMESTAMPDIFF(SECOND, MIN(endzeit), abholzeit) + TIMESTAMPDIFF(SECOND, abholdatum, enddate) AS diff
   FROM bestellungen AS early
LEFT JOIN (
    SELECT abholzeit AS endzeit, abholdatum AS enddate FROM bestellungen
) AS later
WHERE (endzeit > abholzeit
    AND enddate = abholdatum)
    OR enddate > abholdatum
GROUP BY id
ORDER BY abholdatum, abholzeit

WARNING, that last code is still really broken. For example, it could get a time from MIN(endzeit) that is the lowest time, but from a really far away date. An actual solution would be involved. I recommend, if possible, just merging date and time into one column.

Pinelo · Answer 3 · 2014-02-02T06:24:58.803

InoS Heo, Your version works !!

Thank You...

This is what I needed :D

SELECT x.id, x.abholdatum, x.abholzeit AS from_ts, y.abholzeit AS to_ts,
TIME_TO_SEC(CONCAT(x.abholdatum, ' ', y.abholzeit)) - TIME_TO_SEC(CONCAT(x.abholdatum, ' ', x.abholzeit)) AS diff_ts
FROM (
    SELECT @seq := @seq + 1 AS ord, id, abholdatum, abholzeit
    FROM bestellungen, (SELECT @seq := 0) init
    WHERE (abholdatum = DATE_SUB(CURDATE(),INTERVAL -1 DAY)) 
    ORDER BY abholzeit
) x LEFT JOIN  (

    SELECT @seq2 := @seq2 + 1 AS ord, id, abholdatum, abholzeit
    FROM bestellungen, (SELECT @seq2 := 0) init
    WHERE (abholdatum = DATE_SUB(CURDATE(),INTERVAL -1 DAY)) 
    ORDER BY abholzeit
) y ON x.ord = y.ord - 1

Difference in seconds between two times in two rows in the same column - MySql

3 Answers3