Description: 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' );
Replies are listed 'Best First'.
Re: Expand Class::DBI Field to DateTime Object
by Fletch (Chancellor) 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).

      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.

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.