Having this dataset: https://www.db-fiddle.com/f/6vmgx4krsgMRgprDjErdqu/0
I want to have a additional column which shows the time distance to the entry before, how can i achieve this?
Thank you very much in advance :)
As you stated in your fiddle that you use MySQL 5,7
You must use user defined variables.
I selected TIMEDIFF to display the difference because you didn't state which Information you need, so i choose this, but as you have both values you can use different mysql functions
Schema (MySQL v5.7)
CREATE TABLE `someTable` (
`ID` INT,
`POS` INT,
`Date` DATETIME,
`Customer` VARCHAR(64)
);
INSERT INTO `someTable` VALUES
(1, 10, "2017-03-10 08:00:00", "Peter"),
(2, 11, "2017-03-10 08:00:01", "Peter"),
(3, 12, "2017-03-10 08:00:04", "Peter"),
(4, 17, "2017-03-10 08:00:05", "Peter"),
(5, 16, "2017-03-10 08:00:08", "Karl"),
(6, 17, "2017-03-10 08:00:09", "Karl"),
(7, 10, "2017-03-10 08:00:12", "Peter"),
(8, 10, "2017-03-10 08:00:13", "Peter");
SELECT * FROM someTable
Query #1
SELECT
ID,
POS
,`Customer`
,IF(@date = `Date`,0,TIMEDIFF(`Date`, @date)) diff
,@date := `Date` 'Date'
FROM someTable, (SELECT @date := (SELECT MIN(`Date`) FROM someTable)) A;
| ID | POS | Customer | diff | Date |
| --- | --- | -------- | --------------- | ------------------- |
| 1 | 10 | Peter | 0 | 2017-03-10 08:00:00 |
| 2 | 11 | Peter | 00:00:01.000000 | 2017-03-10 08:00:01 |
| 3 | 12 | Peter | 00:00:03.000000 | 2017-03-10 08:00:04 |
| 4 | 17 | Peter | 00:00:01.000000 | 2017-03-10 08:00:05 |
| 5 | 16 | Karl | 00:00:03.000000 | 2017-03-10 08:00:08 |
| 6 | 17 | Karl | 00:00:01.000000 | 2017-03-10 08:00:09 |
| 7 | 10 | Peter | 00:00:03.000000 | 2017-03-10 08:00:12 |
| 8 | 10 | Peter | 00:00:01.000000 | 2017-03-10 08:00:13 |