[sf-perl] [Fwd: class DBI postgres date time format]

[Kevin Frost]

Funny, I was almost going to mention triggers in my first response.

The nice thing about doing it in a trigger is there's no way to forget/ 
break the logic in your application.  The less-nice thing is that you  
have a performance hit per update, which can be a problem in high  
transaction volumes.  It's definitely something you'd want to  
benchmark (triggers vs. Perl w/DateTime vs. Perl w/o DateTime) if you  
expect to be doing a lot of updates.

Also, I would argue that in Fred's example the modified timestamp  
("mts") should not have a default value.  When you create a record but  
do not subsequently modify it, its modification timestamp should be  
NULL, so you can tell the difference.  This of course is highly  
impractical because you then have to remember about the NULLs in all  
your queries for the rest of your life, so it's an argument I often  
lose.

FWIW PostgreSQL is also pretty forgiving in its date casting, and for  
quick hacks and debugging you can often just skip the TO_DATE  
altogether.

And now back to our regularly scheduled Perl programming...

-- f.


On Mar 19, 2009, at 1:37 AM, Fred Moyer wrote:

> 2009/3/18 ken uhl <kenuhl at berkeley.edu>:
>>
>>
>> How do I set up table class method to correctly format and write  
>> 'updated'
>>  column  date and time stamp to postgres?
>
> With PostgreSQL you don't need to deflate an object if you just want
> to mark the time the row was updated.
>
> I have two columns, cts (creation time stamp) and mts (modification
> time stamp).  cts gets set automatically when the row is inserted, and
> mts updates to now() on subsequent updates via a trigger.
>
> cts              | timestamp without time zone | default now()
> mts              | timestamp without time zone | default now()
>
> create or replace function update_modified_column() returns trigger  
> as $$ BEGIN
> NEW.mts = now();
> return new;
> END;
> $$ language 'plpgsql';
>
> create trigger update_table_mts BEFORE UPDATE ON router FOR EACH ROW
> EXECUTE PROCEDURE update_modified_column();
>
> Here's an actual record update, all I did was change a boolean in  
> the record:
>
> cts              | 2007-08-29 12:55:22.028357
> mts              | 2008-08-12 11:58:37.77211
>
> cts              | 2007-08-29 12:55:22.028357
> mts              | 2009-03-19 00:43:54.714981
>
>
> Granted, this approach involves no Perl, but TMTOWTDI, and row
> modification creation and timestamping seems like something that
> should be done in the database (although I need a trigger for each
> table I track the cts and mts.  Modifying other date columns
> represents a chance though to use object inflation and deflation in
> Perl.
>
>>
>> I have table entry object that contains this - to inflate and  
>> deflate :
>>
>> I think I need to add  "has_timestamp"
>> .....
>>
>>    22 __PACKAGE__->table('dhcpmac');
>>    23 __PACKAGE__->columns( Primary => qw/rowid/ );
>>    24 __PACKAGE__->columns( All => qw/rowid mac calnetuid updated  
>> updatedby
>> dynhostname ishmael disable/ );
>>    25
>>    26 __PACKAGE__->has_a( updated => 'DateTime',
>>    27                     inflate => sub {
>> DateTime::Format::Pg->parse_timestamptz(shift); },
>>    28                     deflate => sub {
>> DateTime::Format::Pg->format_timestamptz(shift); } );
>>
>> ...
>>
>> my CGI  does this :
>>
>> ...
>> #  retrieve old row data   301          my $delta_row =
>> IST::DNSDB::dhcpmac->retrieve($old_rowid) ;
>>   302          my $old_mac = $delta_row->mac();
>> ...
>> #  replace some of the data - including 'updated'
>>   305          $delta_row->mac($new_validated_mac);
>>   306              $delta_row->updated( "now()" );
>> ..
>>   308          $delta_row->updatedby($calnetuid);
>> ...
>>   313           $delta_row->update();
>>   314           $delta_row->commit();
>>
>> ( line 306 crashes with invalid SQL command line value, obviously,  
>> but what
>> goes here? )
>>
>> ?
>>
>> TIA,
>> Ken Uhl,
>> UC Berkeley
>>
>>
>>
>>
>> _______________________________________________
>> SanFrancisco-pm mailing list
>> SanFrancisco-pm at pm.org
>> http://mail.pm.org/mailman/listinfo/sanfrancisco-pm
>>
>>
> _______________________________________________
> SanFrancisco-pm mailing list
> SanFrancisco-pm at pm.org
> http://mail.pm.org/mailman/listinfo/sanfrancisco-pm