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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
