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

I've got a mySQL table with rows that will each contain a date/time (currently in time() format). I'd like to be able to input two times in month/day/hour/minute format and return all database entries that fell between those two dates and times.

Is there a way to do this without using Date::Calc, possibly with using a different date/time format in mySQL? Another module? Date::Calc looks like it would be lighting a campfire with an ICBM.

Thanks.

Replies are listed 'Best First'.
(Ovid) Re: unix time and dates with mysql
by Ovid (Cardinal) on Feb 26, 2002 at 23:33 UTC

    Use Time::Local to convert the datetime to seconds from the Epoch. You can than just use your typical numeric comparisons to find out if you fall in a predetermined range. Time::Local is a standard module. However, MySQL will allow you to compare dates directly, if properly formatted, so I'm not sure that we're on the same page.

    Cheers,
    Ovid

    Join the Perlmonks Setiathome Group or just click on the the link and check out our stats.

[reply]
Re: unix time and dates with mysql
by grep (Monsignor) on Feb 26, 2002 at 23:32 UTC
    Why not just keep using epoch seconds (which is what I assume you mean when you say time() format)?. You can convert back and forth between between the epoch secs and Year/Month/Day/Time with localtime and Time::Local. MySQL will treat them as integers. Else you need to convert them to dates and use the MySQL date type and MySQL will do the comparisons as date.

    grep
    grep> grep clue /home/users/*
[reply]

      This sounds like a good idea, but it's not. Someday you might want to access the database from a different client, like Excel or Access, that doesn't deal with epoch seconds natively. Then you will feel much pain.

      It's far better to keep it in the native datetime format and use the DATE_FORMAT() functionality to return the date like you want -- even in epoch seconds :-)

      Chris
      M-x auto-bs-mode

[reply]
[d/l]
Re: unix time and dates with mysql
by cayenne (Scribe) on Feb 27, 2002 at 00:44 UTC

    If I understand your question correctly:

    The mySQL time type will not work for this purpose as it is used to store things that are just in h:m:s format, and won't store large enough times to be useful for dates. However the datetime type would work quite well, and mySQL will have an easy time comparing dates in this format. Example (I think this will work; I haven't tested it out): 

    $sth = $dbh->prepare('SELECT * FROM Table WHERE TimeCol >' . $dbh->quo
+te($begin_time) . 'AND TimeCol <' . $dbh->quote($end_time))

    [download]
    where $begin_time and $end_time are strings of the format "YYMMDDHHMMSS"; the year and second can be filled in by your script so that you still only need to input the month, day, hour, and minute.
[reply]
[d/l]
Re: unix time and dates with mysql
by jonjacobmoon (Pilgrim) on Feb 27, 2002 at 00:33 UTC
    I could be wrong about this, but this sounds like something you can do without perl. If your columns are in date format you should be able to contruct a SQL statment that takes only those entries. Right? Or did I miss something? 

    select * where $date1 < dateCol and $date2 > dateCol

    [download]

    dateCol is the column that holds all your dates and $date1 and $date2 are the inputted dates. I believe that MySQL will hold the full date, including time.

    UPDATE: I believe Cayenne is correct. From the MySQL documentaion: "The DATETIME type is used when you need values that contain both date and time information. MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format."

    I admit it, I am Paco.
[reply]
[d/l]
Re: unix time and dates with mysql
by strat (Canon) on Feb 27, 2002 at 09:48 UTC
    Maybe the fieldformat TIMESTAMP could help you: it contains data in the form yyyymmddhhmmss, which is very easy to parse and to calculate (you can use > and <, if you need to). But the big magic with this field is, when you assign NULL to it, it will contain the current timestamp.
    I use this solution a lot (e.g. for a Javascript/CGI-Chat I'm just playing around, because I'm interested how it can work; you may test it on http://chat.fabiani.net/chat/, although it is by far not finished yet.

    Best regards,
    perl -le "s==*F=e=>y~\*martinF~stronat~=>s~[^\w]~~g=>chop,print"

[reply]

Back to Seekers of Perl Wisdom