What ways are there to do type coercion when sending data to/from databases?

Dates

One of the most basic places that this comes up is when dealing with dates. Within Perl, you often want to use Perl dates (or DateTime objects) (or Time::Piece objects). But with MySQL, you want to use MySQL dates, and Oracle, Oracle dates, etc.

Ways to convert the data:

• DateTime::Format::DBI and related modules

Ways to automatically trigger the conversion:

• Class::DBI::Plugin::DateTime:*
• DBIx::Class::InflateColumn::DateTime
• Class::DBI's inflate and deflate triggers

MySQL ⇔ epoch time conversion

    use DateTime::Format::MySQL;

# Perl (epoch) date => MySQL date
sub typeconv_deflate_datetime {DateTime::Format::MySQL->format_datetime(DateTime->from_epoch(epoch => shift))}

# MySQL date => Perl (epoch) date
sub typeconv_inflate_datetime {DateTime::Format::MySQL->parse_datetime(shift)->set_time_zone('UTC')->epoch()}

MySQL ⇔ DateTime conversion

    use DateTime::Format::MySQL;

# DateTime date => MySQL date       (explicitely force UTC timezones for MySQL dates)
sub typeconv_deflate_DateTimeobject {DateTime::Format::MySQL->format_datetime(shift->set_time_zone('UTC'))}

# MySQL date => DateTime date       (explicitely force UTC timezones for MySQL dates)
sub typeconv_inflate_DateTimeobject_gmtime {DateTime::Format::MySQL->parse_datetime(shift)->set_time_zone('UTC')}
sub typeconv_inflate_DateTimeobject_localtime {typeconv_inflate_DateTimeobject_gmtime(shift)->set_time_zone('America/Chicago')}