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

This is an update to an earlier SOPW.

I need to calculate 24 hours ago for localtime as the string Day, time date, etc in text format.

I can remove people from the database 5 minutes of age but I cannot remove people that are 24 hours old. For some reason, it held the number of people for the first day just fine. But now it jumped down to less people than appear in the db that stores the past 5 minutes.

Something is wrong.. still.. and I am getting frustrated. I NEED users to be in day_time for 24 hours and I NEED users to be in now_time for 5 minutes.

Would it be easier to calculate TODAY as opposed to past 24 hours? For example, it will restart every day at midnight server time? If so, how would I do that?

This is my code.

$minutes = $minutes * 60; # minutes = 5 prior
my $shorttime = localtime( time() - $minutes );
my $data = qq(DELETE FROM now_time WHERE time < "$shorttime");

my $sth = $dbh->prepare($data);
$sth->execute() or die $dbh->errstr;

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

my $day = localtime( time() - 24*60*60 );

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

[download]

Replies are listed 'Best First'.
Re: calculating 24 hours ago with localtime
by McDarren (Abbot) on Dec 09, 2005 at 07:54 UTC
    Maybe it's just me, but I find this question extremely difficult to understand. Let me attempt to paraphrase:
    • You have 2 tables: now_time and day_time
    • You want to remove records from now_time where the "time" field is older than 5 minutes.
    • You want to remove records from day_time where the "time" field is older than 24 hours

    If the above assumptions are correct, then I wouldn't bother messing around with localtime. I'd just do it with the sql. Not sure which db you are using, but with mysql the following would suffice: 

    DELETE FROM time_now WHERE time < (NOW() - INTERVAL 5 MINUTE);
DELETE FROM day_now WHERE time < (NOW() - INTERVAL 1 DAY);

    [download]

    Apologies if I've misunderstood your question. If that's the case, perhaps you could clarify a bit.

[reply]
[d/l]
      using NOW(), how would I setup the database then? Right now I am using time VARCHAR(25) for the time field and I am inserting the $localtime as a string.

      Yes, this is MySQL and you are right. Two tables. One for the last 5 minutes. One for the last 24 hours.

[reply]
        using NOW(), how would I setup the database then? Right now I am using time VARCHAR(25) for the time field and I am inserting the $localtime as a string.

        When you create your database table, use a datetime data type for your time field.

        The mysql NOW() function returns the current date/time, so you simply use this for both your INSERTs and DELETEs

        Cheers,
        Darren :)

[reply]
Re: calculating 24 hours ago with localtime
by svenXY (Deacon) on Dec 09, 2005 at 09:15 UTC
    Hi,
    just make it a TIMESTAMP field.
    One of the good things about it is that if you do not explicitely it, it will be autmatically filled with the current time. Note that a MySQL timestamp is not a Unix Timestamp!
    Regards,
    svenXY
[reply]

      Whenever I have to work with MySQL, I avoid TIMESTAMP like the plague. I initially thought it was a bug, but it seems to be a "feature" to update the first TIMESTAMP column on an update (or insert).

      --
      b10m

      All code is usually tested, but rarely trusted.
[reply]
Re: calculating 24 hours ago with localtime
by Delusional (Beadle) on Dec 09, 2005 at 13:49 UTC
    Based on the code, it appears that the date is written as human readable. If you used time in seconds (enteries will look like 1134035260 in the db), you can take something like:
    my $maxhour = time() - (24*60*60);
my $data = qq(DELETE FROM day_time WHERE time <= "$maxhour");

    [download]
    This difference is, is you would query the db for a long number verses a string, which may be the reason its currently failing.

    Printing the time in human readable format could be done with my $TOL = sprintf "%d d, %d h, %d m \& %d s\n",(gmtime $dbdata{day_time})[7,2,1,0]; print $TOL;. This assumes you use Time::Local;.
[reply]
[d/l]
[select]
Re: calculating 24 hours ago with localtime
by TedPride (Priest) on Dec 09, 2005 at 16:28 UTC
    As I probably already said in the other thread, most people just use a number field and insert a time() value. This makes (relative) time-based calculations so much easier, since you can just add or subtract from time() or NOW() and give mySQL a nice efficient numeric comparison to do.
[reply]

Back to Seekers of Perl Wisdom