Jasen Betts wrote:
> electricity meter may bis a bad example as usage meters often have
> fewer digits than are needed to track all historical usage
>
> eg:
>
> '2012-05-07',997743
> '2012-06-06',999601
> '2012-07-05',000338
> '2012-08-06',001290
> '2012-09-07',002158
> '2012-10-05',003018
Wrap-around can be handled pretty easily. It's meter replacement
that is a challenge. :-)
SELECT
current_reading_date as "reading date",
lag(current_meter_reading, 1)
over (order by current_reading_date) as "prior reading",
current_meter_reading as "current reading",
(1000000000 + current_meter_reading
- lag(current_meter_reading, 1)
over (order by current_reading_date)) % 1000000 as usage
from electricity;
reading date | prior reading | current reading | usage
--------------+---------------+-----------------+-------
2012-05-07 | | 997743 |
2012-06-06 | 997743 | 999601 | 1858
2012-07-05 | 999601 | 338 | 737
2012-08-06 | 338 | 1290 | 952
2012-09-07 | 1290 | 2158 | 868
2012-10-05 | 2158 | 3018 | 860
(6 rows)
-Kevin