Now, from what I understand, you would like a 'date' column to be treated like any other column in regards that you can assign a string version of a date to the mutator, and when you stringify the date, it returns it in a consistent format. Plus it needs to work properly with MySQL. We can get very close to that using the has_a relationship.

The issue with using inflate is that the database provides the date in a different format then you want to provide it in, and inflate will be used for both instances (unless you provide an object to the mutator). So to solve that we need to provide an 'inflate' method that understands both types of input, and can distinguish between them. The simples way of doing that is with something like the following:

use Time::Piece::MySQL;
use Date::Parse;
__PACKAGE__->has_a(
    date    => 'Time::Piece',
    inflate => sub {
            Time::Piece->new(Date::Parse::str2time(shift));       
    },
    deflate => 'mysql_datetime',
);
[download]

Here I have used the Date::Parse module to do the parsing , and it passes a unix timestamp off to the Time::Piece module. I have quickly tested this and it seems to work, except that the Date::Parse modules parses strings of the form xx/xx/xxxx as MM/DD/YYYY which is not what you were after. However, I think it illustrates what can be accomplished when allowing 'inflate' to be able to parse multiple date formats... By the way, you could accomplish the same thing using Date::Simple, or some of the other Date modules on CPAN. Time::Piece is just what I happen to use.

The other half of the problem has to do with stringification. Time::Piece when stringified calls 'cdate'. You could override this somehow to stringify to your own favourite format. This is just an example, and it is potentially dangerous in a shared environment like mod_perl, since you would be changing the stringification for all other modules using Time::Piece within the same process. But here it goes anyway:

package MyTime;

use strict;
use Time::Piece;

package Time::Piece;

use overload '""' => sub { shift->dmy("/"); },
             'cmp' => \&str_compare,
             'fallback' => undef;

1;
[download]

This just changes the overloaded "" to a different function that outputs in a format that you like. To use this you would add the following to your Class::DBI table module:

package MyDBI::Table;

use base 'Class::DBI';
use Time::Piece::MySQL;
use MyTime;
use Date::Parse;

__PACKAGE__->has_a(
    date    => 'Time::Piece',
    inflate => sub {
            Time::Piece->new(Date::Parse::str2time(shift));
    },
    deflate => 'mysql_datetime',
);
[download]

And when using this object, you can read from and assign to it like it is a regular string:

my $t = MyDBI::Table->retrieve(1);
print $t->date, "\n";
$t->date('2003-07-08');
print $t->date, "\n";
$t->date('07/08/2003'); # read as MM/DD/YYYY
print $t->date, "\n";
[download]

This is not perfect... You are still dealing with an object, so you need to be careful when passing it to other functions if they expect a string. However, since it is an object, you also have great flexibility in how to access the date field.