Re^3: (OT) Dearest Monks - Mysql Question - Compare current insert record with last record inserted.

Yes - unless there's a HUGE calculation penalty you really don't want to store data that can be calculated.

(For a simple example, suppose your code glitches and stores several incorrect prices. Easy enough to fix, just get the correct prices and replace. If you're storing calculated price differences, you also need to recalculate. OTOH, if you had a view that did the calculation for you, once you've updated the price the view is automatically fixed.)

You use SQL to compare the prices between rows. Personally, I'd use that SQL to create a view. A good example of such SQL is at MySQL Cookbook.

Update:

If your tables do not have a unique ID column (other than the timestamp), you may need somewhat more awkward SQL. For example, if a table has only two column - timestamp and price - the SQL might be:

select new.timestamp, new.price,
       new.price - old.price as price_change
from tablename new
left join tablename old
on old.timestamp = (
  select max(sub.timestamp)
  from tablename sub
  where sub.timestamp < new.timestamp
)
[download]

Comment on Re^3: (OT) Dearest Monks - Mysql Question - Compare current insert record with last record inserted. Download Code

Replies are listed 'Best First'.
Re^4: (OT) Dearest Monks - Mysql Question - Compare current insert record with last record inserted. by jdlev (Scribe) on Nov 19, 2009 at 21:13 UTC
I actually used the epoch as the unique ID...will that make all this easier? I love it when a program comes together - jdhannibal	[reply]
Re^5: (OT) Dearest Monks - Mysql Question - Compare current insert record with last record inserted. by keszler (Priest) on Nov 20, 2009 at 02:33 UTC
By epoch do you mean the unix-style timestamp - the number of seconds since Thu Jan 1 00:00:00 1970 UTC? ("The epoch" refers to that exact date/time - see Unix Time.) ( `perl -e "print scalar gmtime(0);" Thu Jan 1 00:00:00 1970` [download] ) If so, and if (and that's an enormous if) the timestamps are always exactly 5 minutes apart, you could change the sub-select clause to `old.timestamp = new.timestamp - 300` [download] Otherwise, the sub-select for the previous-in-time row will have to do. If your tables had an ID column that automatically incremented by one for each row, the sub-select could be replaced by 'old.id = new.id - 1' as in the MySQL Cookbook examples. Of course that assumes that rows are never inserted out of order, like when the network connection drops for a few hours and you have to go back later and add the missing data . . .	[reply] [d/l] [select]