0

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!

laminatefish
  • 5,197
  • 5
  • 38
  • 70

2 Answers2

2

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.

fancyPants
  • 50,732
  • 33
  • 89
  • 96
  • Excellent answer, thank you! :) Yeah, I'd actually just finished writing a self join, I thought it looked really messy, but it's pretty identical! SELECT lu_sensor_log.ID, lu_sensor_log.BOX_COUNT, lu_sensor_log.`TIME`, TIMEDIFF(lu_sensor_log.`TIME`, t2.`TIME`) AS Difference FROM lu_sensor_log LEFT JOIN lu_sensor_log as t2 ON t2.ID=lu_sensor_log.ID-1 ORDER BY lu_sensor_log.`TIME` DESC; – laminatefish Aug 02 '13 at 11:28
  • @fancyPants I just saw your answer and I was wondering if you could answer [this](http://stackoverflow.com/questions/27152671/how-to-compare-ids-in-the-same-table-without-join-in-mysql) question too :) I based my code on your answer but I am missing something. – sokras Nov 26 '14 at 15:23
  • also what happens if the ID is not a primary key and there can be many tuples with the same id? – sokras Nov 26 '14 at 17:50
  • Maybe I can have a look at it tomorrow :) this is no promise, I'm quite busy at the moment. – fancyPants Nov 26 '14 at 22:46
0

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

jinoth edwin
  • 1