0

I am new to MySQL but have found it quite addictive. I have 1 table that logs DateTime, Sensor Type and ID as Auto inc primary key. I would like Time Diff between logs but I'm stuck on how to work around gaps in my ID column. I tried to add row_number but found I can't inner join on that because it doesn't really exist. Would Re Numbering another column in code be a work around? The table has about 3000 records and grows each day. I'm using MS Access as a front end with an ODBC to connect with. Which can be slow.

SELECT 
     (@row_number:=@row_number + 1) AS rnum,
     g1.LogDT CurLogDT,
     g2.LogDT NexLogDT,
     g1.Sensor,
     g1.ID,
     timestampdiff(second,g1.LogDT,g2.LogDT) as Diff 
FROM
     AllSensorsTbl g1
     inner join
     AllSensorsTbl g2 on g2.ID = g1.ID + 1 , (SELECT @row_number:=0) AS t
where
     g1.Sensor = "sump pump"

This is what the above returns

| rnum | ID |      CurLogDT    |     NexLogDT     |  Diff |   Sensor  |
|  604 |906 | 11/27/15 19:39:35| 11/28/15 01:32:18| 21163 | sump pump |
|  605 |907 | 11/28/15 01:32:18| 11/28/15 03:23:23| 6665  | sump pump |
|  606 |914 | 11/28/15 19:56:21| 11/29/15 03:25:34| 19753 | sump pump |
|  607 |1050| 12/26/15 09:57:03| 12/26/15 15:10:24| 18801 | sump pump |
|  608 |1051| 12/26/15 15:10:24| 12/26/15 16:42:26| 5522  | sump pump |

IDs 907 914 1050 is where the NexLogDT Time is not what I want. I need it to follow the rnum column.

Drbellows1020
  • 1
  • 1
  • why are you concerned with id gaps – Drew Sep 24 '16 at 21:06
  • Also you are mixing explicit join syntax with an old-style join comma join for your derived table `t` (the vars). Stick with explicit – Drew Sep 24 '16 at 21:09
  • @Drew The gaps are what cause the nexLogDT value to be incorrect. You can see it better on ID 914 to 1050. As for the Syntax goes, I'm not aware of that. I was pulling code that I found and tried to make it work with what I needed. Still learning. – Drbellows1020 Sep 24 '16 at 21:32
  • A description of the [difference](http://stackoverflow.com/a/1599201) of join styles. If you have a comma in your `from clause` then it is old-school. As for gaps, you will never avoid them trust me. Apart from deletes, there are easily a dozen scenarios that will force them so embrace them. Please see this small write-up of mine [Here](http://stackoverflow.com/a/38363271) as you say you are addicted to mysql now. – Drew Sep 24 '16 at 21:39
  • @Drew Will do Thanks. – Drbellows1020 Sep 24 '16 at 21:42

0 Answers0