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

I am using a MySQL database to store information on machines on the network. I have one table that contains the core information, determining whether to probe the machines for more information. The main piece of data I would like to use is a timestamp. And be able to then do date comparisions. If the timestamp greater than a given date.

What I want to know is that if I take the value from the MySQL timestamp I should be able to treat it like an integer. It is stored in a format that as time moves in the future the appeared value is always greater.

This seems like the most efficient way to process this data. But having little experience with timestamps and knowing the experience out there. Can anyone suggest a better approach.

Sorry about the lack of code I am designing it now and just wonder what is the best way to dig into it. My searches on it seem to give varied resulsts and most are concerned with converting the timestamp for display. Thank you in advance.

UpdateKey is a bad choice of words. Key as in key to the probelm.

The first paragraph has been redone it was:
I am using a MySQL database to store information on machines on the network. I have one table that contains the key information determining whether to probe the machines for more information. The key I would like to use is a timestamp. And be able to then to date comparisons. Has this machine been scanned since date foo set in the program.

Replies are listed 'Best First'.
Re: MySQL Timestamp comparison
by dbwiz (Curate) on Oct 30, 2003 at 13:01 UTC

    You can see MySQL timestamps as numbers. 

    my $query = "SELECT timestampColumn FROM mytable LIMIT 1";

my $sth = $dbh->prepare($query);

$sth->execute();

while (my $row = $sth->fetchrow_arrayref()) {
    my $TS = $row->[0];
    print "$TS ",   $TS + 1, " ",  $TS + 10, "\n";
}

__END__
20030524183051 20030524183052 20030524183061

    [download]

    Be aware, though, that timestamps in MySQL can't deal with time intervals smaller than one second. Depending on how fast is your server, you may have several thousand records with the same timestamp. Just to give you a hint, in my laptop, I managed to insert 100,000 records in 2.6 seconds. In my company's server, I did the same operation in 0.8 seconds. Of course, ALL those records have the same timestamp.

[reply]
[d/l]
Re: MySQL Timestamp comparison
by tune (Curate) on Oct 30, 2003 at 12:48 UTC
    I think it is a very bad idea. Consider you are having more than one record inserts within a secundum. You cannot issue a unique key to your new records since it would be identical. You better use an auto_increment integer as primary key, and keep the timestamp for other reasons. The auto_increment column is strictly growing in time too, and does not take up as much disk space.

    Hey wait, where is the perl question in this???

    --
    tune

[reply]
Re: MySQL Timestamp comparison
by jeffa (Bishop) on Oct 30, 2003 at 18:40 UTC
    If you decide that you do want to manipulate MySQL timestamps in your Perl code, you will definitely want to check out Time::Piece::MySQL.

    jeffa

    L-LL-L--L-LL-L--L-LL-L--
-R--R-RR-R--R-RR-R--R-RR
B--B--B--B--B--B--B--B--
H---H---H---H---H---H---
(the triplet paradiddle with high-hat)
[reply]
Re: MySQL Timestamp comparison
by hmerrill (Friar) on Oct 30, 2003 at 13:58 UTC
    Dates/Times tend to be handled differently by different databases. I've always tried to stay database-neutral in terms of date/time handling - a technique I like to use is to convert timestamp fields into "seconds since the epoch" which is an integer number of seconds since 1/1/1970. Then doing date calculations are easy. MySQL has a function(MySQL specific) called UNIX_TIMESTAMP() which returns the date as the number of seconds since the epoch. If you use a TIMESTAMP or DATETIME column, you can write a subroutine to convert that into an "seconds since the epoch" number.
[reply]
      If you use a TIMESTAMP or DATETIME column, you can write a subroutine to convert that into an "seconds since the epoch" number.

      This isn't necessary. The UNIX_TIMESTAMP MySQL function takes a datetime (any date type column) as an argument and returns the epoch of the time passed; such as: SELECT UNIX_TIMESTAMP(myDateCol) FROM TABLE;

      Also, MySQL has another function called INTERVAL that allows you to do math on the date in the database itself, such as: select * from table where myDateCol < current_date - interval 5 day; will show you everything older than five days ago.

      When doing database programming, the hardest thing for me to get over was to try and do everything in the program (in Perl, in C, etc) when the database is (in _most_ cases) going to be able to do it for you a lot more efficiently and a lot faster. Let the database do it's job.. it likes it, really.

[reply]
        The point in creating a subroutine to take in a date/time and return an "seconds since the epoch", is not so much for MySQL specifically, but more for portability - other databases don't have a UNIX_TIMESTAMP() function, and therefore can't give back a date/time column in "seconds since the epoch". So your subroutine can function in a date/time portability role - to convert a date/time, say coming in in YYYY-MM-DD HH:MM:SS format, into a number that is "seconds since the epoch".
[reply]

Back to Seekers of Perl Wisdom