Expand Class::DBI Field to DateTime Object

One of my favorite features of Class::DBI is the ability to inflate/deflate fields into any object. For instance, your date or time columns can be expanded into DateTime objects. The snippet below creates a method which, when called with a column name, will expand that column into a DateTime object. It requires the DateTime::Format::DBI module and the DateTime::Format module for your database. Within your application, whatever date field you get out of the database will automagically become a DateTime instance.

package My::Database; # Main Class::DBI module
use base 'Class::DBI';

# Other Class::DBI code here

sub is_datetime 
{
        my $class = shift;
        my $field = shift || return;

        use DateTime::Format::DBI;
        my $dtf = DateTime::Format::DBI->new( $class->db_Main );
        $class->has_a( $field => 'DateTime',
            inflate  => sub { $dtf->parse_datetime(   shift ) },
            deflate  => sub { $dtf->format_datetime(  shift ) },
        );
}


package My::Database::SomeTable;
use base 'My::Database';

# Other Class::DBI code here

__PACKAGE__->is_datetime( 'timestamp' );
[download]

Comment on Expand Class::DBI Field to DateTime Object Download Code

Replies are listed 'Best First'.
Re: Expand Class::DBI Field to DateTime Object by Fletch (Bishop) on Jul 15, 2004 at 15:59 UTC
Be careful that you don't do this to fields which are primary keys, as that'll make Class::DBI most unhappy (that one took a good while until I tracked down the admonition against doing so in the docs).	[reply]
Re^2: Expand Class::DBI Field to DateTime Object by hardburn (Abbot) on Jul 15, 2004 at 17:25 UTC
I'm a little skeptical of using a date as a primary key (unless it's a composite one), but I'm sure someone out there could think up a reason. Actually, I'm against the idea of using any meaningful data as a primary key. Auto-incremented fields are much safer. ---- send money to your kernel via the boot loader.. This and more wisdom available from Markov Hardburn.	[reply]
Re: Expand Class::DBI Field to DateTime Object by tomhukins (Curate) on Sep 10, 2004 at 14:22 UTC
This approach works really well, but I've been caught out by the way it handles unknown or undefined times. MySQL stores unrecognised date/times as `0000-00-00 00:00:00`, as described in the MySQL manual. In the case of TIMESTAMP fields, unknown values are also stored in this form, as opposed to using `NULL`. I've worked around this problem by storing timestamps that may not exist in external tables and referencing them using Class::DBI's `might_have` relationship. I've raised a bug report for this in RT. Although it's a documented feature of MySQL, I appreciate the argument that DateTime::Format::MySQL shouldn't have to work around it.	[reply] [d/l] [select]

Back to Cool Uses for Perl