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

I'm rather new to using MySQL instead of DB-File and have a question.

I have records that I want stored and sorted by time. I'm going to have a pulldown where the user can either see TODAY'S data, all data in the last 7 days (week), and then by each of the last 12 months.

My question is, how would I store the time so I could sort by this? Would I just save $time = localtime(); and store it like this? 

  my $data = qq(INSERT INTO searches (search, engine, time) values(?,?
+,?));
  my $sth = $dbh->prepare($data);
  $sth->execute("$search", "$engine", $time;

[download]
It would make sense if I could save it where the month name would be stored so I wouldn't have to write a snippet to convert it back to month names for the print out.

Anyway, I just need to know how I'd store the timefield with an example because I'm new to mysql.

Replies are listed 'Best First'.
Re: sorting mysql by week and year
by jdtoronto (Prior) on Aug 09, 2006 at 00:51 UTC
    Go to MySQL's website here and look up the documentation on data types. MySQL has specific field types for date, time, date-time and timestamp as well as a raft of date/time manipulation functions.

    jdtoronto

[reply]
      I have read the docs, that's why I posted here for examples. For someone new to this, docs aren't very helpful. They are always written towards people who already know what they're doing or have a clue.

      I have made an attempt but it fails. It says there's Undefined subroutine &main::NOW. Can you or anyone else tell me what's wrong? 

      use DBI;
my $dbh = DBI->connect("DBI:mysql:$dbase", $mysql_user, $mysql_pass) o
+r
print DBI->errstr;

  my $data = qq(INSERT INTO searches (search, engine, time) values(?,?
+,?));
  my $sth = $dbh->prepare($data);
  $sth->execute("$search", "$engine", NOW());

      [download]
[reply]
[d/l]

        Well, in the example you gave, you're trying to call a perll function named 'NOW'. If you're using bind variables, you can't substitute in a function call, even if you quote it. (it'll just think it's a string ... or at least it should ... if yours doesn't, something bad is happening, as it might let other bad code through, too.)

        Anyway, try the following:

          my $data = qq(INSERT INTO searches (search, engine, time) values(?,?
+, NOW() ));
  my $sth = $dbh->prepare($data);
  $sth->execute($search, $engine);

        [download]

        (Note -- If $search and $engine are objects, you might have to stringify them, but if they're already strings, you don't need to put them in double quotes.)

        Now for the off topic part -- if you're trying to track insertion time in the database, you can use a TIMESTAMP field to handle it. As for dealing with the months, just use the MONTH() function or FORMAT_DATE() with %M.

        (yes, I could tell you how to use FORMAT_DATE(), but learning to read the documentation is good for you -- as you know what you're looking for, you should be able to find it. You have to get over your issues with reading documentation, or you'll have to depend on other people whenever you run into problems)

[reply]
[d/l]
Re: sorting mysql by week and year
by gam3 (Curate) on Aug 09, 2006 at 02:35 UTC
    I use Date::Calc::Object and then you can use join('-', $datetime->date); to get the date in mysql format. For time you use join(':', $datetime->time).
    Date::Calc is light weight and does alot of useful data and time calculations.
    Storing localtime() in the database is almost always a bad idea, as you can't use the mysql date and time functions if you need them.
    With split you can get the mysql dates and times back into Date::Calc.
    -- gam3
    A picture is worth a thousand words, but takes 200K.
[reply]
Re: sorting mysql by week and year
by ides (Deacon) on Aug 09, 2006 at 16:14 UTC

    This isn't really a Perl question, but a SQL one. You don't need to worry about how the data is STORED, but how it is retrieved.

    Store it as whatever timestamp like value you want and then when you pull the data out to present it to the user you'll need to qualify your SELECT statement to only include the rows that match what you want. I'm not very familiar with MySQL, but it should be something like this: 

    SELECT * FROM table WHERE date = 'today'; 
SELECT * FROM table WHERE date >= '08-01-2006' # 7 days ago
SELECT * FROM table WHERE date >= '08-08-2005' # 12 months ago

    [download]

    Frank Wiles <frank@revsys.com>
    www.revsys.com

[reply]
[d/l]
Re: sorting mysql by week and year
by gcalexander (Novice) on Aug 09, 2006 at 18:00 UTC
    You don't have to worry about how you store the date. Leave the sorting up to the DB when you retrieve the data that you need. What is the 'time' column in the searches table, an Integer, a DATETIME, TIME, DATE? If its a a DATETIME field then this should do the trick 
    my $query = qw/INSERT INTO searches (search, engine, time) values (?,?
+,NOW())/;
$sth = $dbh->prepare($query);
$sth->execute($search, $engine);

    [download]
    To retrieve these: 
    my $sth = $dbh->prepare (<<EOSQL);
SELECT search, engine, DATE_FORMAT(time, "%W the %D of %M, at %r") as 
+formatted_date from searches
EOSQL
$sth->execute;

    [download]
    This will return formatted_date as "Thursday the 10th of August, at 16:45 PM" You should probably check out the date/time functions page on the MySQL web site.
[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom