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

Good evening monks!!

I've got a small question here.
I'm currently working on a application that checks out webpages for errors and such and uploads the info gathered to a database. Its almost done! But I'm just missing one thing. I can't get the datetime to be uploaded to the database. So, here's what I've got:

When I use this line: my $time = localtime(time); the resulting string is like this: Wed Mar 30 00:13:59 2005 . However, I need that info to look like this: 2005-03-30 00:13:59. I know...this is REALLY simple stuff :S Now, the problem is that I am clueless as to how I can manage to work with this string bit by bit, reformatting every "word" to create a string understandable by MySQL.

First I would like to know if there is any module out there that can do this form me. I've checked in CPAN under "datetime" search, but I don't seem to find anything of interest. If there is none, I can do it myself right now, but for that, I would need someone to explain me first how to go through the string, word by word.

Replies are listed 'Best First'.
Re: Converting localtime to SQL format
by jbrugger (Parson) on Mar 30, 2005 at 07:08 UTC
    If you want to put the current time / date in a MySQL database, why don't you create a table with a fieldtype date/time and insert your query using now()?
    eg: 
    # Datefield is of type: datetime
my $sth = $dbh->prepare('insert into tableName (DateField, OtherField)
+ values (now(),?)');

    [download]


    "We all agree on the necessity of compromise. We just can't agree on when it's necessary to compromise." - Larry Wall.
[reply]
[d/l]
      That was simple :S
      Here I was reading hundreds of tutorials x_X
      Thanks a lot jbrugger :)
[reply]

      For the perpetually lazy, most databases have some way of defining default values. (although, with dynamic values such as this, you might have to use a trigger).

      In mysql, you can just use the column type TIMESTAMP.

      It's been a while since I've played with oracle, but if setting the column 'DEFAULT SYSDATE' doesn't work. (It might've been 'DEFAULT SYSDATE()'), you can always use an insert trigger.

[reply]
[d/l]
[select]
Re: Converting localtime to SQL format
by brian_d_foy (Abbot) on Mar 30, 2005 at 07:35 UTC
[reply]
      Thats interesting :)
      thanks a lot
[reply]
Re: Converting localtime to SQL format
by blazar (Canon) on Mar 30, 2005 at 08:41 UTC
    In addition to the good answers you already got, I think it is worth reminding that most perl functions return different values according to the context. In this case it wouldn't be too hard to roll your own(TM) solution: 
    my $time = do {
    my ($s,$m,$h,$D,$M,$Y)=localtime;
    $Y+=1900;
    $M++;
    "$Y-$M-$D $h:$m:$s" };

    [download]
    UPDATE: I added $M++ which was not not there as per crenz's warnings.
[reply]
[d/l]
[select]

      You provide a good reason to rather use Posix::strftime(): Your solution is wrong since the month is zero-based and you forgot to increment the month.

[reply]
        You provide a good reason to rather use Posix::strftime(): Your solution is wrong since the month is zero-based and you forgot to increment the month.
        Indeed. Mine was more of an example especially aimed at reminding of different contexts.
[reply]
Re: Converting localtime to SQL format
by jasonk (Parson) on Mar 31, 2005 at 15:15 UTC

    Nobody has mentioned yet Time::Piece::MySQL, which makes localtime() return an object, so you can just do this:

    use Time::Piece::MySQL;
print localtime->mysql_datetime."\n";
print localtime->mysql_date."\n";
print localtime->mysql_time."\n";

    [download]

    I generally prefer this to NOW() because I'm usually working with Class::DBI objects, rather than building my own queries, and you have to jump through hoops to get NOW() in there without getting quoted.

    We're not surrounded, we're in a target-rich environment!
[reply]
[d/l]

Back to Seekers of Perl Wisdom