NAME

DBIO::InflateColumn::DateTime - Auto-create DateTime objects from date and datetime columns.

VERSION

version 0.900005

SYNOPSIS

Load this component and then declare one or more columns to be of the datetime, timestamp or date datatype.

package Event;
use base 'DBIO::Core';

__PACKAGE__->load_components(qw/InflateColumn::DateTime/);
__PACKAGE__->add_columns(
  starts_when => { data_type => 'datetime' },
  create_date => { data_type => 'date' }
);

Then you can treat the specified column as a DateTime object.

print "This event starts the month of ".
  $event->starts_when->month_name();

If you want to set a specific timezone and locale for that field, use:

__PACKAGE__->add_columns(
  starts_when => { data_type => 'datetime', timezone => "America/Chicago", locale => "de_DE" }
);

If you want to inflate no matter what data_type your column is, use inflate_datetime or inflate_date:

__PACKAGE__->add_columns(
  starts_when => { data_type => 'varchar', inflate_datetime => 1 }
);

__PACKAGE__->add_columns(
  starts_when => { data_type => 'varchar', inflate_date => 1 }
);

It's also possible to explicitly skip inflation:

__PACKAGE__->add_columns(
  starts_when => { data_type => 'datetime', inflate_datetime => 0 }
);

NOTE: Don't rely on InflateColumn::DateTime to parse date strings for you. The column is set directly for any non-references and InflateColumn::DateTime is completely bypassed. Instead, use an input parser to create a DateTime object. For instance, if your user input comes as a 'YYYY-MM-DD' string, you can use DateTime::Format::ISO8601 thusly:

use DateTime::Format::ISO8601;
my $dt = DateTime::Format::ISO8601->parse_datetime('YYYY-MM-DD');

See t/inflate_column_datetime.t for a runnable example covering inflation, deflation, and the inflate_datetime/inflate_date opt-in and opt-out flags shown above.

DESCRIPTION

This module figures out the type of DateTime::Format::* class to inflate/deflate with based on the type of DBIO::Storage::DBI::* that you are using. If you switch from one database to a different one your code should continue to work without modification (though note that this feature is new as of 0.07, so it may not be perfect yet - bug reports to the list very much welcome).

If the data_type of a field is date, datetime or timestamp (or a derivative of these datatypes, e.g. timestamp with timezone), this module will automatically call the appropriate parse/format method for deflation/inflation as defined in the storage class. For instance, for a datetime field the methods parse_datetime and format_datetime would be called on deflation/inflation. If the storage class does not provide a specialized inflator/deflator, [parse|format]_datetime will be used as a fallback. See "Formatters And Stringification" in DateTime for more information on date formatting.

One channel per column type

Every column type has a channel of its own: the methods parse_<type> and format_<type> of the storage's datetime parser. The type name is the data_type in lower case, without precision, with each run of spaces written as _: datetime2(7) is the channel datetime2, time with time zone is time_with_time_zone.

A column whose data_type is not among date, datetime, timestamp, smalldatetime, timestamp with time zone, timestamptz and timestamp without time zone inflates only when it asks for it with inflate_datetime, inflate_timestamp or inflate_date; such a type without a flag is left alone. The column then goes to its own channel when the parser has the method, each direction on its own. When the parser lacks it, the requested kind decides: inflate_date uses parse_date and format_date, any other request parse_datetime and format_datetime. So a varchar column with inflate_date => 1 is read through parse_date. The listed types fall back to parse_datetime and format_datetime. A time of day never falls back to them, see "A time of day stands on 1970-01-01".

A driver offers a type of its own by giving its datetime format class the two methods, see "Offering a type of its own" in DBIO::Storage::DateTimeFormat.

A date stands at noon

A date has no time of day. A column read through the date channel (a date column, or inflate_date => 1 where the parser provides no channel for the column type) inflates to a floating DateTime at 12:00:00, not at midnight: the calendar day the parser read, whatever time and time zone it returned with it. Noon is the time of day at which a conversion to another time zone by less than 12 hours either way leaves the calendar day as it was; the timezone of the column is applied after that. A day stays a day when written, too: a value written through the date channel is not converted to the timezone of the column; the calendar day it has in its own time zone is written. 2026-10-25 00:30 in Europe/Berlin in a date column with timezone => 'UTC' is written as 2026-10-25. The DateTime object passed in is left as it is.

A database whose date type carries a time of day, as Oracle's DATE does, says so through "datetime_date_carries_time" in DBIO::Storage::DBI. Its date columns keep the time they were read and written with and follow the timezone of the column like a datetime.

A time of day stands on 1970-01-01

A column of type time, time without time zone, time with time zone or timetz stays a string unless it asks for inflation with inflate_datetime or inflate_timestamp. Asked for, it goes through the channel of its type (time, time_without_time_zone, time_with_time_zone, timetz), then through parse_time/format_time of the storage's datetime parser (a type with a time zone only through methods that carry the offset, such as parse_timetz/format_timetz), each direction on its own. Where the parser has no such method the core reads and writes itself: HH:MM:SS with an optional fraction, for a type with a time zone followed by Z, +HH or +HH:MM; it writes the fraction only when it is not zero, without trailing zeros, and the offset as +HH:MM. A time of day does not use parse_datetime or format_datetime.

The inflated DateTime stands on 1970-01-01, the day of the Unix epoch that DateTime::Format::Pg and JDBC give a time of day too, whatever date the parser returned; the date never reaches the database. Without a time zone the value is floating, with one it keeps the offset it was read with, as a fixed offset. The timezone of the column does not convert a time of day, neither when read nor when written; the locale applies. A value is written with the time it has in its own time zone, and the DateTime object passed in is left as it is. A floating value in a column with a time zone is written without an offset, and the database applies its own rule.

DateTime and DateTime::Format::Strptime are only recommends of the core distribution, not requirements: a bare DBIO install does not carry them. Install DateTime to use this component at all. A driver whose datetime format class parses by strptime patterns (DBIO::Storage::DateTimeFormat) needs DateTime::Format::Strptime as well, and throws at the first parse or format when it is missing; the driver distribution should declare it itself.

For more help with using components, see "USING" in DBIO::Manual::Component.

METHODS

register_column

Chains with the "register_column" in DBIO::Row method, and sets up datetime columns appropriately. This would not normally be directly called by end users.

In the case of an invalid date, DateTime will throw an exception. To bypass these exceptions and just have the inflation return undef, use the datetime_undef_if_invalid option in the column info:

"broken_date",
{
    data_type => "datetime",
    default_value => '0000-00-00',
    is_nullable => 1,
    datetime_undef_if_invalid => 1
}

COLUMN INFO

data_type => 'datetime' | 'timestamp' | 'date'

Triggers automatic DateTime inflation/deflation. Variants like timestamp with time zone, timestamptz, timestamp without time zone and smalldatetime are recognised and routed to the appropriate parse/format method on the storage class, with parse_datetime and format_datetime as fallback.

inflate_datetime => 1 / inflate_timestamp => 1 / inflate_date => 1

Forces inflation regardless of data_type. The column goes to the channel of its own type (see "One channel per column type"); where the parser has none, inflate_date falls back to parse_date/format_date, the others to parse_datetime/format_datetime; a time of day follows "A time of day stands on 1970-01-01". Useful when storing dates in varchar columns. Set to 0 to opt out for a column whose data_type would otherwise trigger inflation.

timezone => $tz, locale => $loc

Set on the inflated DateTime object. When a value is written, a copy of it is converted to the timezone; the DateTime object passed in keeps its zone. A date and a time of day are not converted, see "A date stands at noon" and "A time of day stands on 1970-01-01".

floating_tz_ok => 1

Suppress the warning when assigning a floating-timezone DateTime to a column with a fixed timezone.

datetime_undef_if_invalid => 1

Return undef on parse failure instead of throwing.

USAGE NOTES

If you have a datetime column with an associated timezone, and subsequently create/update this column with a DateTime object in the DateTime::TimeZone::Floating timezone, you will get a warning (as there is a very good chance this will not have the result you expect). For example:

__PACKAGE__->add_columns(
  starts_when => { data_type => 'datetime', timezone => "America/Chicago" }
);

my $event = $schema->resultset('EventTZ')->create({
  starts_at => DateTime->new(year=>2007, month=>12, day=>31, ),
});

The warning can be avoided in several ways:

Fix your broken code

When calling set_time_zone on a Floating DateTime object, the timezone is simply set to the requested value, and no time conversion takes place. It is always a good idea to be supply explicit times to the database:

my $event = $schema->resultset('EventTZ')->create({
  starts_at => DateTime->new(year=>2007, month=>12, day=>31, time_zone => "America/Chicago" ),
});

Suppress the check on per-column basis

__PACKAGE__->add_columns(
  starts_when => { data_type => 'datetime', timezone => "America/Chicago", floating_tz_ok => 1 }
);

Suppress the check globally

Set the environment variable DBIO_FLOATING_TZ_OK to some true value.

Putting extra attributes like timezone, locale or floating_tz_ok into extra => {} has been DEPRECATED. Instead put it directly into the columns definition like in the examples above. If you still use the old way you'll see a warning - please fix your code then!

SEE ALSO

More information about the add_columns method, and column metadata, can be found in the documentation for DBIO::ResultSource.

Further discussion of problems inherent to the Floating timezone: Floating DateTimes and $dt->set_time_zone

AUTHOR

DBIO & DBIx::Class Authors

COPYRIGHT AND LICENSE

Copyright (C) 2026 DBIO Authors Portions Copyright (C) 2005-2025 DBIx::Class Authors Based on DBIx::Class, heavily modified.

This is free software; you can redistribute it and/or modify it under the same terms as the Perl 5 programming language system itself.

cpanm DBIO

perl -MCPAN -e shell
install DBIO