users online script not accurate

Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

I posted this some time ago and I just got back to working on this personal project.

Below is a section of my users online script. It accurately reports the number of users currently online but it does NOT remove data from 24 hours ago. The script is supposed to keep just the last 24 hours of user data in the database and delete everything else.

Is there something perhaps with the logic?

my $timenow = time();
$minutes = $minutes * 60;
my $timemin = $timenow - $minutes;

my $data = qq(DELETE FROM now_time WHERE time < "$timemin");
my $sth = $dbh->prepare($data);
$sth->execute() or die $dbh->errstr;

my $day = 60 * 60 * 24;
my $timeday = $timenow - $day;
#print "Time day: $timeday <br> day: $day <br> Time: $timenow";

my $data = qq(DELETE FROM day_time WHERE time < "$timeday");
my $sth = $dbh->prepare($data);
$sth->execute() or die $dbh->errstr;
[download]

Comment on users online script not accurate Download Code

Replies are listed 'Best First'.
Re: users online script not accurate by cowboy (Friar) on Nov 07, 2005 at 19:55 UTC
Look through the documentation for your database, and let it do the date calculations for you. For example, in mysql, you would do: `DELETE FROM day_time WHERE time < DATE_SUB(NOW(), Interval 24 Hour)` [download] This saves you from doing your own calculations, and makes sure you don't get bitten by the webserver and db server having clocks that are not in sync. (in the example I gave above, you're trusting the db to be the authoritive source)	[reply] [d/l]
Re^2: users online script not accurate by tweetiepooh (Hermit) on Nov 08, 2005 at 11:17 UTC
Agreed. In Oracle the current date/time is sysdate which is day based `delete from table where dateattr < sysdate - 1;` [download] In systems like Oracle I'd probably use stored procedures and get Oracle to fire it off automatically. If there is other processing then you may need to call it via perl. Also don't forget to COMMIT if required. DBI can autocommit and some systems will also commit on disconnect but it's better practice to do it yourself. That way you have control.	[reply] [d/l]
Re: users online script not accurate by ickyb0d (Monk) on Nov 07, 2005 at 19:22 UTC
I haven't dealt with time stuff in SQL recently.I believe all of the time functions (in perl) give you an epoch number (seconds since January 1, 1970). So make sure that the comparison that you are using in your SQL table is comparable. If not you may need to format your epoch time to look like the one in your SQL table. Also, I don't think you need to necessarily do a prepare/execute combo when just deleting something. If you wanted you could just use a $dbh->do(statement) Hope this helps	[reply]
Re: users online script not accurate by parv (Parson) on Nov 08, 2005 at 04:12 UTC
> my $timenow = time(); > $minutes = $minutes * 60; How do you get 'em $minutes? > my $timemin = $timenow - $minutes; Do you realize that you are subtracting minutes from a value which is in seconds (since Unix epoch)?	[reply]