Setting Date and Time NULL with Rose :: DB :: Object and MySQL

Question

I may be wrong here, but it seems that there are conflicting standards.

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

But Rose :: DB :: Object uses DateTime fields for MySQL DATETIME, and trying to set the DATETIME value to zero with "0000-00-00" throws an exception in the DateTime module. those. I cannot create a DateTime object with year 0, month 0, day 0, because it throws an exception in the DateTime module.

I checked in Rose :: DB :: Object :: Metadata :: Column :: Datetime and I see no way to explicitly handle NULL DateTime when creating a record or when retrieving it.

Did I miss something?

those. can Rose :: DB :: Object handle NULL datetime fields (MySQL), although DateTime (Perl module) cannot.

Code example:

#!/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 RoseDB :: dt_test module:

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 started it, I get the error "Invalid datetime:" 0000-00-00 "in line tmp.pl 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 recover the MySQL NULL 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)

It seems that problems with tables in which datetimes are defined using NOT NULL and then try to use MySQL's false zero are a problem. I'm too tired to play with it now, but I'll see what happens when I change the table structure in the morning.

Answer 1

datetime 0000-00-00 00:00:00 :

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

" " datetime Rose::DB::Object, . datetime MySQL 0000-00-00 00:00:00 datetime.

: 0000-00-00 date, datetime ( timestamp) : 0000-00-00 00:00:00

, undef :

$o->mycolumn(undef);

, NOT NULL, save() .

Answer 2

MySQL ( , ). "0000-00-00" - , NULL MySQL. , IS NULL?

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

:

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