The problem I was working on is solved, but felt it would be worthwhile to ask for some opinions as to whether the approach can actually be improved.
I am using the following example and data taken from SQL datetime calculations and MySQL 5.1:
CREATE TABLE `data` (
`id` INT NOT NULL AUTO_INCREMENT,
`datetime` datetime NOT NULL,
`temperature` float NOT NULL,
`change` float NOT NULL,
PRIMARY KEY (id)) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `data` (`datetime`, `temperature`) VALUES ('2009-01-01 06:07:08','70.4')
,('2009-01-01 06:08:08','62.1')
,('2009-01-01 06:09:08','78.8')
,('2009-01-01 07:08:09','72.6')
,('2009-01-01 07:08:10','63.2')
,('2009-01-01 07:08:11','79.9')
,('2009-01-01 08:09:10','69.3')
,('2009-01-01 11:12:13','64.3')
;
Now, to calculate the changes in temperature, the following query can be used:
UPDATE data AS D1, data AS D2 SET D1.change = D1.temperature - D2.temperature WHERE D1.id = D2.id + 1;
This works as designed, but requires the need for the data to be sorted first by datetime and - for performance reasons - the addition of an auto-increment called id. Perhaps there are other approaches, which are used commonly in situations where changes in data need to be calculated?