CODEWITHSUNDEEP
mysqltimestamp
laminatefish
laminatefish

Reputation: 5246

Mysql Calculate time difference between timestamps in same field?

Is it possible to calculate the time difference from a timestamp in the same field? My SQL knowledge isn't bad, but I can't figure out how I'd go about doing such a thing on the same table.

ID, SENSOR, COUNT, TIMESTAMP
'1461630', '1', '91', '2013-08-02 09:14:30'
'1461629', '1', '92', '2013-08-02 09:13:29'
'1461628', '1', '92', '2013-08-02 09:12:27'
'1461627', '1', '91', '2013-08-02 09:11:26'
'1461626', '1', '91', '2013-08-02 09:10:24'
'1461625', '1', '7', '2013-08-02 09:03:14'
'1461624', '1', '13', '2013-08-02 09:02:12'
'1461623', '1', '13', '2013-08-02 09:01:11'
'1461622', '1', '7', '2013-08-02 09:00:09'
'1461621', '1', '3', '2013-08-02 08:58:06'

What I need to do, is display a pie-chart with UP vs Down time values. I only have 1 table to reference, so it would all have to be on minute intervals, given only the timestamp above.

Specifically, with the times below, the machine wasn't running for around 7 mins. It's this 7 mins I have to figure out.

'1461626', '1', '91', '2013-08-02 09:10:24'
'1461625', '1', '7', '2013-08-02 09:03:14'

Is this possible? Or would I really need a blow-by-blow account in a separate table? Obviously, I'd rather not create more tables, because the device I'm working with is really quite limited, and it's already a massive hit for it to report this data to a tcp server I have running.

Obviously TIMESTAMPDIFF(,) doesn't work as I need two reference points, whereas I've only got the one. I'm imagining some kind of dodgy sub-select scenario, but I'm not sure.

Cheers!

Upvotes: 0

Views: 2285

Answers (2)

jinoth edwin
jinoth edwin

Reputation: 1

SELECT *,TIMESTAMPDIFF(second,max(date(timestamp)),min(date(timestamp))) as secondsdifference FROM yourTable

Upvotes: 0

fancyPants
fancyPants

Reputation: 51888

You can self-join the table, something like this works in your case:

SELECT 
*
FROM
yourTable a
INNER JOIN yourTable b ON a.ID = b.ID + 1
WHERE TIMESTAMPDIFF(second, a.timestamp, b.timestamp) > 60

But this can get ugly when you have gaps in your ID column. And especially it can get ugly (in terms of performance (when you don't have good indexes on the table)) when you have *lots of data.

So, I'd suggest using a bit more advanced queries using variables. Without the need to join the table to itself this typically runs pretty fast:

SELECT * FROM (
SELECT
yt.*,
TIMESTAMPDIFF(second, @prevTS, `timestamp`) AS timedifference,
@prevTS:=yt.`timestamp`
FROM
yourTable yt
, (SELECT @prevTS:=(SELECT MIN(`timestamp`) FROM yourTable)) vars
ORDER BY ID
)subquery_alias
WHERE timedifference > 65

To further improve this query to display the two rows where timedifference is too big shouldn't be a problem :) When you get in serious trouble, feel free to ask, though.

Upvotes: 2

Related Questions