>>>>> "Paulo" == Paulo Castro <[EMAIL PROTECTED]> writes:
Paulo> I am in a great trouble inserting a DATETIME value in a mySQL
Paulo> table from a perl script.
As described in the info page "(mysql)Column types":
| `DATETIME'
| A date and time combination. The supported range is `'1000-01-01
| 00:00:00'' to `'9999-12-31 23:59:59''. *MySQL* displays
| `DATETIME' values in `'YYYY-MM-DD HH:MM:SS'' format, but allows you
| to assign values to `DATETIME' columns using either strings or
| numbers.
MySQL defaults to using the ISO-8601 date format, which is "yyyy-mm-dd
hh:mm:ss" ["hh" being a 24-hour clock], and is described excellently
at:
http://www.cl.cam.ac.uk/~mgk25/iso-time.html
As such, you can always insert a value that looks like this into a
DATE field in MySQL. Here's how I did it:
| {
| my $_static_init;
| my $_sth;
|
| sub upgrade_queue_action
| {
| my ($self, $id, $new_action) = @_;
|
| unless ($_static_init)
| {
| my $u_sql = ( 'UPDATE spider_queue' .
| ' SET action = ?, retry_at = ?' .
| ' WHERE url_id = ?' );
| unless ($_sth = $self->{dbh}->prepare($u_sql))
| {
| cluck "can't prep \"$u_sql\": $DBI::errstr";
| return;
| }
|
| $_static_init = 1;
| }
|
| # to make sure that we don't immediately try it again...
| my $new_retry_at = strftime
| ( '%Y-%m-%d %T', localtime(time() + 120 + int(rand(120))));
|
| return $_sth->execute($new_action, $new_retry_at, $id);
| }
| }
Taking a quick look at your code:
Paulo> ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time);
Paulo> $year += 1900;
Paulo> my $date = sprintf('%04d/%02d/%02d %02d:%02d:%02d',
Paulo> $year,$month,$mday,$hour,$min,$sec);
We can see that you're not using precisely the format it expects.
Also, note that MySQL has a built-in function "FROM_UNIXTIME" that
groks the unix time_t metric, so you can use time_t values directly by
doing something like this:
my $i_sql = 'INSERT INTO a_table ( a_datetime_column ) ' .
' VALUES ( FROM_UNIXTIME(?) )';
$mysql_dbh->do($i_sql, {}, $time_t);
See info page "(mysql)Date and time functions":
| `FROM_UNIXTIME(unix_timestamp)'
| Returns a representation of the `unix_timestamp' argument as a
| value in `'YYYY-MM-DD HH:MM:SS'' or `YYYYMMDDHHMMSS' format,
| depending on whether the function is used in a string or numeric
| context.
|
| mysql> select FROM_UNIXTIME(875996580);
| -> '1997-10-04 22:23:00'
| mysql> select FROM_UNIXTIME(875996580) + 0;
| -> 19971004222300
t.
