[Dbix-class] DateTime Inflations for Timestamps

[Ryan Cone]

On May 4, 2009, at 5:15 AM, Alexander Hartmaier wrote:
> Am Freitag, den 01.05.2009, 00:48 +0200 schrieb Matt S Trout:
>> On Thu, Apr 30, 2009 at 03:53:52PM -0400, Ryan Cone wrote:
>>>
>>> I am using DBIC with Oracle and I am trying to distinguish between
>>> columns that are Date and Date Time accurate.  In the database and  
>>> the
>>> models, I am using Date and Timestamp to represent the two types.   
>>> But
>>> a bit of unlucky coincidence prevents me from loading
>>> InflateColumn::DateTime as a component...
>>>
>>> 1.  InflateColumn makes the timestamp a datetime.
>>> 2.  DateTime::Format::Oracle makes the datetime a date (rightly so I
>>> think since dates and datetimes are synonymous in Oracle).
>>>
>>> For now, I am adding custom Inflates using  
>>> DateTime::Format::Oracle's
>>> parse_date and parse_timestamp and those work as expected.  But
>>> eventually it would be nice to have something that can be loaded.
>>
>> How about patching InflateColumn::DateTime so that if there -are-
>> parse_timestamp and format_timestamp methods it uses those, and  
>> then it
>> falls back to the _datetime methods if not?
>>
>> Looks to me like we simply didn't realise that there were  
>> DateTime::Format::
>> modules that had separate timestamp support - in fact more  
>> accurately *I*
>> didn't realise that at the time.
>>
>> Do the other Oracle users on here want to weigh in on this please?
>>
>
> I use only 'DATE' columns (for the non-oracle guys, this stores date
> *and* time, stupid naming) and have a similar issue, I only want to
> store a date.
> Is there an in-/deflator for date-only columns?
>
> --
> BR Alex

This is how you could do it provided the file was patched as Matt  
suggested (or you inflate manually).

You can make the DATE columns behave like they are date-only by  
setting $ENV{'NLS_DATE_FORMAT'} = 'YYYY-MM-DD' (or something similar  
without time units).  This will strip all time data for inflations/ 
deflations of columns defined in your classes as DATE and DATETIME.

For columns that require time accuracy, you can set and use  
$ENV{'NLS_TIMESTAMP_FORMAT'} = 'YYYY-MM-DD HH24:MI:SS' provided you  
have changed those columns to TIMESTAMPs in Oracle and your classes.   
This is the part that does not currently work when loaded as  
InflateColumn.  It does work using DateTime::Format::Oracle- 
 >parse_timestamp directly.

Matt, I think your idea looks like the way to go and I see you already  
provided access to result_source->storage->datetime_parser.  For  
clarification, were you suggesting I patch it or are you interested in  
updating the package.  I'd prefer not to create a fork if I can avoid  
it.