(OT) Removing something from MySQL table based on time

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

I am not very good with MySQL yet and I'm not sure if the direct problem is because of that or my logic is messed up.

I want a USERS ONLINE script. It shows how many people are online within the past 5 minutes. It also shows how many uniques were shown 24 hours ago (not every day, but 24 hours ago from whatever time it is).

The users online part works but the 24 hour users don't seem to be resetting. Everyday the number grows and grows and grows. So is my logic messed up for clearing all data from over 24 hours ago?

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

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

my $day = 60 * 60 * 24;
my $timeday = $timenow - $day;

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

2005-09-14 Retitled by g0n, as per Monastery guidelines
Original title: 'Removing something from MySQL table based on time'

Comment on (OT) Removing something from MySQL table based on time Download Code

Replies are listed 'Best First'.
Re: (OT) Removing something from MySQL table based on time by pg (Canon) on Sep 09, 2005 at 00:41 UTC
First, use binding valuables, so your queries become: `delete from now_time where lasttime < ? delete from day_time where lasttime < ?` [download] This is a suggestion for the future. But for this particular case, what you should really do is to calculate the time difference by using MySQL internal functions, not to calculate in Perl and pass to MySQL. Read MySQL document and look for the section for date and time functions. Pay attention to ADDDATE(), ADDTIME(), CURDATE() and CURTIME(). Your query should become something like: `delete from day_time where lasttime < CURTIME() - INTERVAL 1 DAY; delete from now_time where lasttime < CURTIME() - INTERVAL 5 MINUTE;` [download]	[reply] [d/l] [select]
Re: (OT) Removing something from MySQL table based on time by McDarren (Abbot) on Sep 09, 2005 at 02:11 UTC
Whenever you're writing a query that's going to do a DELETE, it's always a good idea to write it as a SELECT first, just so that you can see exactly what it is that will be deleted. This is especially true with mysql, as there is no ROLLBACK function - so unless you've backed up your data you only have one chance to get it right. So taking your example, I'd do: SELECT FROM day_time WHERE lasttime < "$timeday"; and see what that returns. Also agree with the suggestions re using mysql date functions. -- Darren	[reply]
Re: (OT) Removing something from MySQL table based on time by bradcathey (Prior) on Sep 08, 2005 at 23:24 UTC
What is a sample of what is in lasttime? Have you looked at that value? —Brad "The important work of moving the world forward does not wait to be done by perfect men." George Eliot	[reply]
Re^2: (OT) Removing something from MySQL table based on time by Anonymous Monk on Sep 08, 2005 at 23:34 UTC
`id ip lasttime 2020 216.32.90.218 1126216551 1455 216.32.90.218 1126216551 2017 216.32.90.218 1126216551 1916 216.32.90.218 1126216551 ...` [download] Last time always looks like a similar number. Thank you.	[reply] [d/l]