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

Answer 1

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)