CODEWITHSUNDEEP
mysqlintervalstimestamp
solick
solick

Reputation: 2345

Differences between timestamps in MySQL Table

I have a question regarding interval calculation with timestamps:

Let´s assume I have a table with IDs and timestamps:

Table tab1

tab1.id    deviceid    timestamp
-------    --------    ----------
1            15         2013-01-01 14:57:54
2            15         2013-01-01 14:58:09
3            23         2013-01-01 14:58:10
4            15         2013-01-01 14:58:30

What I want to do is to check if the intervals between the entries are inside or outside a specific value. Let´s assume 15 sec is the value:

The interval between 1 and 2 is OK, between 2 and 3 not OK.

Any hints how to solve this without using a temporary table and stored procedure etc.?

Thanks in advance and kind regards

solick

EDIT: Updated the table. There are timestamps from other devices in between the entries.

Upvotes: 0

Views: 159

Answers (2)

solick
solick

Reputation: 2345

Solution:

njk give me the right hint, but at the end it was necessary to use a stored procedure and temoprary table:

Important here: Temoprary tables cannot be used more than once within a query, therefore i needed to copy the temporary table:

BEGIN

DROP TEMPORARY TABLE IF EXISTS tmptable1;
DROP TEMPORARY TABLE IF EXISTS tmptable2;

CREATE TEMPORARY TABLE tmptable1 (tmpid INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY)
SELECT * FROM tbl_source WHERE tbl_source.Id = did;

CREATE TEMPORARY TABLE tmptable2 AS (SELECT * FROM tmptable1);

SELECT a.tmpid, a.timestamp, TIME_TO_SEC(TIMEDIFF(b.timestamp, a.timestamp)) AS time_diff
FROM tmptable1 a LEFT JOIN tmptable2 b
ON b.tmpid = a.tmpid +1
WHERE b.tmpid IS NOT NULL;

DROP TEMPORARY TABLE IF EXISTS tmptable1;
DROP TEMPORARY TABLE IF EXISTS tmptable2;


END

Upvotes: 0

Kermit
Kermit

Reputation: 34055

You can start with this. This will join the table against itself and calculate the +1 id to calculate the difference in seconds.

SELECT a.id, 
   TIME_TO_SEC(TIMEDIFF(b.timestamp, a.timestamp)) AS time_diff
FROM tab1 a
LEFT JOIN tab1 b ON b.id = a.id + 1
WHERE b.id IS NOT NULL

Result

| ID | TIME_DIFF |
------------------
|  1 |        15 |
|  2 |        21 |

SQL Fiddle

Upvotes: 2

Related Questions