title: Use MySQL to calculate difference between two entries in the same table
body: <p>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. </p>
  
  <p>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.</p>
  
  <p>so, I currently have:</p>
  
  <p><code>BuildingID | MeterID | Date_and_Time | Reading</code></p>
  
  <p>and I would like to produce:</p>
  
  <p><code>BuildingID | MeterID | Date_and_Time | Time_Since_Previous_Read | Accumulation_Since previous_Read</code></p>
  
  <p>two typical entries might look like this:</p>
  
  <p><code>1 | 1 | 2010-10-09 17:56:20 | 119.6</code></p>
  
  <p><code>1 | 1 | 2010-10-09 18:01:08 | 157.4</code></p>
  
  <p>and I would like to produce:</p>
  
  <p><code>1 | 1 | 2010-10-09 18:01:08 | 00:04:48 | 37.8</code></p>
  
  <p>If no previous entry exists (i.e. for the first reading) i woudl like to rerun zeros for the time elapsed and the accumulation.</p>
  
  <p>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.</p>
  
  <p>thank you</p>