Compare entries 72 hours apart

[JChilds]

( I put this in the mysql section too, wasn't sure which place was best)

 

I have a table with three columns: Timestamp, ID, Amount

 

Every hour, new data is written to the table.

The ID always stays the same, but the Timestamp(obviously) and Amount are changing.

 

eg:

2008-06-24 08:00:00     1     100
2008-06-24 09:00:00     1     176
2008-06-24 08:00:00     2     120
2008-06-24 09:00:00     2     134

 

I want to be able to be able to retrieve all the ID's where the amount hasn't changed in the last 72 hours.

 

I have no idea how to approach this, so if anyone could point me in the right direction, I'd appreciate it very much.

[JChilds]

Has anyone even got an idea of where I should be looking for an answer?

[Barand]

try

SELECT a.`timestamp`, a.id, a.amount
FROM mytable a
JOIN mytable b 
ON  a.`timestamp` = b.`timestamp` + INTERVAL 72 HOUR 
    AND a.id = b.id 
    AND a.amount = b.amount

 

This will tell you if it is the same 72 hours later but it won't tell if it changed in between. You'll need to check separately for that.