Timezone Issues with DateTime, DBIx::Class and SQLite (Time Off by 5 Hours)

Question

I'm using a DBIx::Class schema generated by dbcidump to make inserts and queries.

Recently, I discovered that all of my times are stored +5 hours ahead of our local time (est).

In my Result class, I have __PACKAGE__->load_components("InflateColumn::DateTime"), where the column is defined as "action_time", { data_type => "datetime", is_nullable => 0}...

Is there an easy way to make it so I can control the timezone DateTime objects are being inflated / deflated with so that my queries and inserts are consistent?

I have tried adding time_zone => 'local' to the column definition, but that makes no difference.

eg:

• insert 2012-12-12 10:04:03
• retrieve 2012-12-12 15:04:03

sample data:

sqlite> select datetime(action_time,'localtime'),action_time from actions order by id desc limit 3;
2012-12-12 08:35:07|2012-12-12 13:35:07
2012-12-12 08:34:45|2012-12-12 13:34:45
2012-12-12 08:34:43|2012-12-12 13:34:43

EDIT: I believe this has something to do with my use of: http://search.cpan.org/dist/DBIx-Class/lib/DBIx/Class/ResultClass/HashRefInflator.pm ; from the docs:

Column value inflation, e.g., using modules like DBIx::Class::InflateColumn::DateTime, is not performed. The returned hash contains the raw database values.

Therefore, I'm wondering how to get that class to do something special for datetime fields.

Answer 1

Your time zone is probably America/New_York, which unlike -0500 ("EST"), follows DST.

For that very reason, it's not a suitable time zone for storage. There are 120 minutes each year where time stamps in this time zone would be ambiguous. So it makes sense that the time stamps are actually stored in UTC.

If you were to use DBIx::Class::InflateColumn::DateTime or if you explicitly created DateTime objects from the time stamps, you could convert these time stamps into your time zone, or any others.

There's an option you can specify when using the schema generator that will cause this module to be used (-o components='["InflateColumn::DateTime"]' for dbicdump).