CODEWITHSUNDEEP
mysqltimestamp
Helmuteke
Helmuteke

Reputation: 73

Mysql Show records where timestamp interval is lower then 4 minutes

let's assume i have follow table

| ID | teamid | timestamp |

| 5 | 1 | 2013-07-27 10:19:00 |

| 6 | 2 | 2013-07-27 10:20:00 |

| 7 | 1 | 2013-07-27 10:25:00 |

| 8 | 3 | 2013-07-27 10:26:00 |

| 9 | 1 | 2013-07-27 10:28:00 |

| 10 | 2 | 2013-07-27 10:29:00 |

| 11 | 3 | 2013-07-27 10:30:00 |

| 13 | 3 | 2013-07-27 10:31:00 |

What i need is the records where the interval between the timestamp is lower then 4 minutes and grouped by the team id

so output need looks like

| 7 | 1 | 2013-07-27 10:25:00 |

| 9 | 1 | 2013-07-27 10:28:00 |

| 11 | 3 | 2013-07-27 10:30:00 |

| 13 | 3 | 2013-07-27 10:31:00 |

can someone show me the correct way to solve

tnx

Upvotes: 0

Views: 115

Answers (1)

Mirco Widmer
Mirco Widmer

Reputation: 2149

The following sql statement will return your desired list:

SELECT table1.id, table1.teamid, table1.timestamp FROM exampleTable table1, exampleTable table2 where table1.id != table2.id AND table1.teamid = table2.teamid AND ABS(table1.timestamp - table2.timestamp) < 400 ORDER BY teamid, id

Upvotes: 1

Related Questions