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

Replies are listed 'Best First'.
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]

Replies are listed 'Best First'.

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

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]

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]