my $min_old = time-60;
my $sth = $dbh->prepare("SELECT * FROM table WHERE timestamp>$min_old"
+);
[download]

cLive ;-)

Automatic updating of the first TIMESTAMP column occurs under any of the following conditions:

* The column is not specified explicitly in an INSERT or LOAD DATA INFILE statement.
* The column is not specified explicitly in an UPDATE statement and some other column changes value. (Note that an UPDATE that sets a column to the value it already has will not cause the TIMESTAMP column to be updated, because if you set a column to its current value, MySQL ignores the update for efficiency.)
* You explicitly set the TIMESTAMP column to NULL.

TIMESTAMP columns other than the first may also be set to the current date and time. Just set the column to NULL, or to NOW(). "

All you have to do is store the time you last checked the database in a text file, and update the text file with the new timestamp whenever you make a check.

Triggers are not production-ready yet. They're in the 5.x series, which is still under new development.

Being right, does not endow the right to be rude; politeness costs nothing.
Being unknowing, is not the same as being stupid.
Expressing a contrary opinion, whether to the individual or the group, is more often a sign of deeper thought than of cantankerous belligerence.
Do not mistake your goals as the only goals; your opinion as the only opinion; your confidence as correctness. Saying you know better is not the same as explaining you know better.

well that sucks. I guess I've been spoiled doing all my db development in Oracle

Another option is to maintain an auditing table. Ideally, you would use triggers to maintain this, but this can also be maintained within the application as well. Basically, every time you modify the table in question, you insert a record into the audit log. Then, you can check to see if the audit log has grown in the intervening period.

My question is why you want to know this - what problem are you trying to solve? There is probably an easier way to solve it than what you're trying to do ...

It would probably be simpler to do something like UPDATE CounterTable SET Counter=Counter+1 when you do an INSERT. You can find the changes by selecting with the clause ORDER BY auto_increment_field DESC or ORDER BY creation_date_field DESC, and reading as many records as the difference in Counter since the last time you checked.

Caveat: Some databases allows you to specify value for an auto-increment field. You can't use that feature if you want ORDER BY auto_increment_field DESC to work.

Maybe you want to read the SQL log for that?

Edit:
And I dreamed it was that easy. You would have to parse the binary Update log instead, so I hope there is an easier way to solve your problem.

Cheers, Sören

The mysqlbinlog utility will parse the binlog for you and takes all sorts of arguments like a start and stop time for instance. You don't need to but wrapping it with XS would be easy.

cheers

tachyon

What's to figure out?

# Make a connection to the database;
# While not killed by a signal
#  Check to see if there are any new records, and do 
#    something with the new records;
#  Sleep for some period;
# Close the connection to the database.
[download]

Update: Well, my comments were intended for some definition of new. I was leaving the details of the solution to the Original Poster, as the problem definition was itself somewhat vague. If that wasn't useful, I'll try to reply to open-ended nodes like this one less often.

"Check to see if there are any new records" ain't easy!! Can't just count them, cause it must support DELETEs.