in reply to 5 minutes of time

You can approach time in databases many different ways..

But if you're just starting out, and just want to conquer a simple problem - like how to delete rows 5 minutes old, then I suggest a simple way, and it's compatible with every database you'll ever come across:

  CREATE TABLE mytable
  (
      person VARCHAR(20),
      lasttime INTEGER
  );

[download]

When you want to insert a row with the current time, run a query like this:

  my $sql = "INSERT INTO mytable (person,lasttime) VALUES ?, ?";
  my $sth = $dbh->prepare( $sql );
  if ( $sth && $sth->prepare( $person, time() ) )
  {
      ; # success
  }
  else
  {
      die( "DB Error " . $dbh->errstr . "\n" );
  }

[download]

When you want to delete a row just run a query like:

  my $timenow = time();
  my $fiveminutes = 5 * 60; # 60 seconds times five
  my $timethen = $timenow - $fiveminutes;

  my $sql = "DELETE FROM mytable WHERE lasttime < ?";
  my $sth = $dbh->prepare( $sql );
  if ( $sth && $sth->execute( $timethen ) )
  {
      ; # success
  }
  else
  {
      die( "DB Error " . $dbh->errstr . "\n" );
  }

[download]

Replies are listed 'Best First'.
Re^2: 5 minutes of time
by weierophinney (Pilgrim) on Jun 04, 2005 at 12:15 UTC
    While I agree with this to an extent, MySQL also has a ton of functions for modifying and comparing dates. The TIMESTAMP type tends to be useless for what the OP is looking for -- as it gets updated each time the record is updated. However, DATETIME does not, and it becomes fairly trivial to write SQL that can do date/time comparisons on such fields. The reason I would prefer to use the SQL over using perl in this instance is that it's portable; if I choose to use another language, I can use the same SQL for doing the comparison.

    I leave it up to the OP to look up the date and time functions; they're very well documented on the MySQL site. (Which is where s/he should have started looking, really... :-)

[reply]

In Section Seekers of Perl Wisdom