Use MySQL to calculate difference between two entries in the same table

I have a series of meter reading stored in a table, each is identified by a building ID, a meter ID and the time at which it was recorded.

For each entry I would like to search for the entry that has the same ID numbers and the closest previous time, I would then like to use the previous time and the previous reading to calculate the length of the time step and the differential between the two readings.

so, I currently have:

开发者_JAVA百科BuildingID | MeterID | Date_and_Time | Reading

and I would like to produce:

BuildingID | MeterID | Date_and_Time | Time_Since_Previous_Read | Accumulation_Since previous_Read

two typical entries might look like this:

1 | 1 | 2010-10-09 17:56:20 | 119.6

1 | 1 | 2010-10-09 18:01:08 | 157.4

and I would like to produce:

1 | 1 | 2010-10-09 18:01:08 | 00:04:48 | 37.8

If no previous entry exists (i.e. for the first reading) i woudl like to rerun zeros for the time elapsed and the accumulation.

I would appreciate very much any help that could be offered on this, I made a concerted effort to find the answer in previous posts but to no avail, feel free to direct me to a good source if this has already been solved elsewhere.

thank you

Maybe like this?

SELECT a.BuildingID, a.MeterID, a.Date_and_Time,
  a.Date_and_Time-b.Date_and_Time AS `Time_Since_Previous_Read`,
  a.Reading-b.Reading AS `Accumulation`,
  MAX(b.Date_and_Time) AS `otherdateandtime`
FROM `TABLENAME` AS `a`, `TABLENAME` AS `b`
WHERE a.BuildingID = b.BuildingID AND a.MeterID = b.MeterID
  AND a.Date_and_Time>b.Date_and_Time
GROUP BY `a.Date_and_Time`

Try this:

/* 1*/SELECT
/* 2*/ r_B.BuildingID, 
/* 3*/ r_B.MeterID, 
/* 4*/ r_B.Date_and_Time, 
/* 5*/ COALESCE(DATEDIFF(hh, r_A.Date_and_Time, r_B.Date_and_Time), 0) AS Time_Since_Previous_Read,
/* 6*/ COALESCE(r_B.Reading-r_A.Reading, 0) AS Accumulation_Since_Previous_Read
/* 7*/  FROM meterdata r_B
/* 8*/  LEFT OUTER JOIN meterdata r_A
/* 9*/  ON r_B.BuildingID = r_A.BuildingID AND r_B.MeterID = r_A.MeterID AND r_B.Date_and_Time > r_A.Date_and_Time
/*10*/  WHERE NOT EXISTS (SELECT nonelater.Date_and_Time FROM meterdata nonelater WHERE nonelater.BuildingID = r_B.BuildingID AND nonelater.MeterID = r_B.MeterID AND nonelater.Date_and_Time > r_A.Date_and_Time AND nonelater.Date_and_Time < r_B.Date_and_Time)
/*11*/ORDER BY r_B.BuildingID, r_B.MeterID, r_B.Date_and_Time

Here's how the design works:

• Lines 7 and 8: the core of the query is a self-JOIN on the meterdata table. You need that to be able to find the difference between values in one row and values in another row. r_B is the later one, r_B the earlier one.
• Line 8: Making it a LEFT OUTER JOIN means that it works even if there isn't an earlier r_A row; that part of the join will return NULLs.
• Line 9: this constrains the JOIN to only join rows for the same building and meter, and makes sure the two rows are the right way around.
• Line 10: If you didn't change the join any more, you'd have each r_B row joining to every single past row. To make sure that r_B matches only to the most recent past row, this line checks that there isn't another row more recent than r_A.
• Line 6: this calculates the difference between the readings; if there isn't an earlier r_A row, this calculation will return NULL, so you need the COALESCE function to change that to zero.
• At line 5, it does the same thing to find the time interval. For this demo I've used the SQL Server DATEDIFF function which won't give you exactly what you want, because on MySQL it only has one option, to calculate the difference in days; you may be able to use the INTERVAL function instead. Again, if there isn't a row r_A then COALESCE will change the NULL to zero.

Everything's there except for getting a time interval out in days, hours and minutes and formatting it nicely. Good luck with that.