[Dbix-class] DateTime Inflations for Timestamps

[Alexander Hartmaier]

Am Montag, den 04.05.2009, 15:44 +0200 schrieb 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.
>
>
>
>
> _______________________________________________
> List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
> IRC: irc.perl.org#dbix-class
> SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
> Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk

I don't want to convert my DATE columns to TIMESTAMP cols because we
partition on it and partitioning by TIMESTAMP is a no-go afaik.
I already set the NLS_DATE_FORMAT to make the inflator work.
One possibility I could think of is to make the deflator strip all time
data...

--
BR Alex


*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*
T-Systems Austria GesmbH   Rennweg 97-99, 1030 Wien
Handelsgericht Wien, FN 79340b
*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*
Notice: This e-mail contains information that is confidential and may be privileged.
If you are not the intended recipient, please notify the sender and then
delete this e-mail immediately.
*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*