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

Hello,

I have three varaibles stored as integers in a MySQL database.

$d1, $d2 , $d3.

Lets say:
$d1 = 09
$d2 = 11
$d3 = 2002


How would I selects all records within the last 28 days. In MySQL, a solution could be this:    $sql = "SELECT * FROM TTD WHERE TO_DAYS(NOW()) - TO_DAYS(date_col) <= 28";

Since the varaibles are not a date column, and are integers, is there anyway I could do this.

I tried:

       $sql = "SELECT * FROM TTD WHERE TO_DAYS(NOW()) - TO_DAYS("$d3-$d1-$d2") <= 28";

Or is there any other method with DBI that could do carry out this job?

Anthony

Replies are listed 'Best First'.
Re: DBI date calculation with MySQL
by Zaxo (Archbishop) on Sep 12, 2002 at 04:03 UTC

    It is awkward keeping a date as three integers when a DATE datatype is available. Your $sql should work if you straighten out the quotes and use stringified values from columns in the date string:

    $sql = q{SELECT *  FROM TTD
         WHERE
           TO_DAYS(CURDATE()) - 
           TO_DAYS(CONCAT_WS('-',YYYY,MM,DD)) <= ?};

    [download]

    I've tossed in a placeholder, and assumed the obvious names for your integer date columns. Note how a datestring is built from your int columns.

    After Compline,
    Zaxo

[reply]
[d/l]
Re: DBI date calculation with MySQL
by Joost (Canon) on Sep 12, 2002 at 13:51 UTC
    As Zaxo mentions, your best bet for storing and comparing dates in MySQL is to use a DATE column, however for some problems using an big integer to store the time (seconds since epoch) can be more intuitive: 

    my $last28days = time - 60*60*24*28;
$sql = "SELECT * FROM TTD WHERE SecSinceEpoch > $last28days";

    [download]

    It could also mean that I need to brush up on my SQL :-)
    YMMV,
    Joost. 

    -- 
Joost       downtime n. The period during which a system
            is error-free and immune from user input.

    [download]
[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom