Geek asks and answers

The difference in seconds between two bisected rows in the same column - MySql

how to do the following

High score table

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 on how to solve this problem?

THX

+3
source share
3 answers

First , you need to make a pivot table as follows.

Here you can check 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 one . Let me calculate the difference in 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)
+1
source

Here is the code if all your lines are on the same date, for example, 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

, , (endzeit) , (abholzeit). id, .

MIN (endzeit), ( , ). TIMESTAMPDIFF, . , .

, . , , , ..

, , . ( ) , :

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

, - . , MIN (endzeit), , . . , , .

0

InoS Heo, your version is working!

Thank...

This is what I need: 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
0
source

More articles:


All Articles