Can Perl DBIx::Class override the way a column is retrieved from the database?

Question

I have never used DBIx::Class until today, so I'm completely new at it.

I'm not sure if this is possible or not, but basically I have a table in my SQLite database that has a timestamp column in it. The default value for the timestamp column is "CURRENT_TIMESTAMP". SQLite stores this in the GMT timezone, but my server is in the CDT timeszone.

My SQLite query to get the timestamp in the correct timezone is this:

select datetime(timestamp, 'localtime') from mytable where id=1;

I am wondering if it is possible in my DBIx schema for "MyTable" to force it to apply the datetime function every time it is retrieving the "timestamp" field from the database?

In the cookbook it looks like it is possible to do this when using the ->search() function, but I am wondering if it's possible to make it so if I'm using search(), find(), all(), find_or_new(), or any function that will pull this column from the database, it will apply the datetime() SQLite function to it?

DBIx::Class seems to have great documentation - I think I'm just so new at it I'm not finding the right places/things to search for.

Thanks in advance!

Answer 1

I've used InflateColumn::DateTime in this way and with a timestamp, and I can confirm it works, but I wonder if you have this backward.

If your column is in UTC, mark the column UTC, and then it should be a UTC time when you load it. Then when you set_timezone on the DateTime (presumably that would be an output issue - it's at output that you care it's locally zoned) you can set it to local time and it will make the necessary adjustment.