perl DBI inserting date\time

Shell Programming and Scripting
1

hi there, my mysql database has a date/time field using the standard mysql date|time format of

2009-08-31 00:16:44

when inserting into this field using perl DBI, is there is an easy way to insert the current date/time in without having to preformat the date/time string in perl before sending it over ??

i tried the NOW() option

my $sth = $dbh->prepare("INSERT INTO network VALUES (\"$serial\", \"$interface\", \"NOW()\")") or die $DBI::errstr;
$sth->execute or die $DBI::errstr;

but the new record has a date|time of

0000-00-00 00:00:00

is there an easy way of doing his

2

If nobody here knows an easier way you could create a function that retrieves the date/time, formats it, and assigns the formatted value to a scalar and interpolate it into your statement handle assignment.

3

thanks ilikecows, I really thought i could use one of the built in mysql time/date functions... ive used now() before with PHP, maybe DBI in perl doesnt support it

---------- Post updated at 09:14 PM ---------- Previous update was at 07:58 PM ----------

I did it this way in the end (in case its useful to others)

my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst)=localtime(time);
my $datetime = printf "%4d-%02d-%02d %02d:%02d:%02d\n",$year+1900,$mon+1,$mday,$hour,$min,$sec;
print $datetime;
4

That's a false statement. Perl DBI supports every single *valid* MySQL statement, if you are connected via the mysql driver.
If you can run the insert statement from the mysql client, you can very well execute it via Perl DBI.

This does the job but adds clutter as well, to your Perl program. More importantly, this picks up the client's local time. So if your MySQL server is in a different timezone, and if you want to add the server's time, then this will work but incorrectly.

tyler_durden