Setting NULL datetime with Rose::DB::Object and MySQL

Question

I could be wrong here, but it looks like there's conflicting standards here.

MySQL treats a stored datetime of "0000-00-00 00:00:00" as being equivalent to NULL. (update - only, it seems, if the datetime is defined as NOT NULL)

But Rose::DB::Object uses DateTime for MySQL DATETIME fields, and trying to set a null DATETIME from "0000-00-00" throws an exception in the DateTime module. ie, I can't create a DateTime object with year 0, month 0, day 0, because this throws an exception in the DateTime module.

I checked in Rose::DB::Object::Metadata::Column::Datetime, and can't see a way of explicitly handling a NULL DateTime when creating an entry or when retrieving.

Am I missing something?

ie, can Rose::DB::Object handle NULL datetime (MySQL) fields even though DateTime (Perl module) can't.

Sample code:

#!/usr/bin/perl
use strict;
use warnings;
use lib 'lib';
use RoseDB::dt_test;

my $dt_entry =  RoseDB::dt_test->new();
$dt_entry->date_time_field('0000-00-00');
$dt_entry->save;



1;

__END__
# definition of table as stored in DB

mysql> show create table dt_test \G
*************************** 1. row ***************************
       Table: dt_test
Create Table: CREATE TABLE `dt_test` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `date_time_field` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

with the RoseDB::dt_test module being:

package RoseDB::dt_test;
use strict;
use warnings;

# this module builds up our DB connection and initializes the connection through:
# __PACKAGE__->register_db
use RoseDB;

use base qw(Rose::DB::Object);

__PACKAGE__->meta->setup (
    table => 'dt_test',

    columns =>
    [
      id              => { type => 'int', primary_key => 1 },
      date_time_field => { type => 'datetime' },
    ],
);

sub init_db { RoseDB->get_dbh }

1;

When I run it, I get the error "Invalid datetime: '0000-00-00' at tmp.pl line 8"

When I change the date to "2010-01-01", it works as expected:

mysql> select * from dt_test\G
*************************** 1. row ***************************
             id: 1
date_time_field: 2010-01-01 00:00:00

I finally managed to recreate the NULL MySQL query example!

mysql> create table dt_test(dt_test_field datetime not null);
Query OK, 0 rows affected (0.05 sec)

mysql> insert into dt_test values(null);
ERROR 1048 (23000): Column 'dt_test_field' cannot be null
mysql> insert into dt_test values('0000-00-00');
Query OK, 1 row affected (0.00 sec)

mysql> select * from dt_test;
+---------------------+
| dt_test_field       |
+---------------------+
| 0000-00-00 00:00:00 |
+---------------------+
1 row in set (0.00 sec)

mysql> select * from dt_test where dt_test_field is null;
+---------------------+
| dt_test_field       |
+---------------------+
| 0000-00-00 00:00:00 |
+---------------------+
1 row in set (0.00 sec)

Looks like the table definitions where the datetimes are defined with "NOT NULL" and then trying to use the MySQL "fake null" is the issue. I'm too tired to play with this now, but I'll see what happens when I change the table structure in the morning.

Answer 1

MySQL allows date types to be incomplete (lacking year, month, and or day). '0000-00-00' is a valid, non-NULL MySQL date. Why do you think it matches IS NULL?

$ echo "select date('0000-00-00') is null" | mysql
date('0000-00-00') is null
0

For comparison:

$ echo "select date('0000-00-32') is null" | mysql
date('0000-00-32') is null
1

Answer 2

You should be able to set a datetime column to the literal desired 0000-00-00 00:00:00 value and save it to the database:

$o->mycolumn('0000-00-00 00:00:00');
$o->save;

Such "all zero" values will not be converted to DateTime objects by Rose::DB::Object, but rather will remain as literal strings. There is no semantic DateTime object equivalent for MySQL's 0000-00-00 00:00:00 datetime strings.

Note: 0000-00-00 is a valid date value, but a datetime (or timestamp) value must include the time: 0000-00-00 00:00:00

To set a column to null, pass undef as the column value:

$o->mycolumn(undef);

Of course, if the column definition in the database includes a NOT NULL constraint, the save() won't work.