class DBI postgres date time format



  • 1. arrays
    I've got an array returned from Google's API and I need to get the data out of it. The best I can do right now is: ARRAY(0x8262e088) Can anyone help me out? - Grant

class DBI postgres date time format

Postby kenuhl » Thu, 19 Mar 2009 03:09:22 GMT

How do I set up table class method to correctly format and write 
'updated'  column  date and time stamp to postgres?

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/ );
     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? )


Ken Uhl,
UC Berkeley


Re: class DBI postgres date time format

Postby paikkos » Thu, 19 Mar 2009 06:49:47 GMT

2009/3/17 ken uhl < XXXX@XXXXX.COM >:
> 3 __PACKA>E__->columns( Prim>ry => qw/rowid/>);
> 4 __PAC>AGE__->columns> All => qw/rowid mac calnetuid updated upda>edby
> dynhostname ishmael disabl>/ );
> 6 _>PACKAGE__->has_a> updated => 'D>teTime',
> 7 > gt;inflate => sub {
> > 8 gt;deflat> => sub {
> DateTime::Format::Pg>>format_timestamptz(shift); } );

I don't recognise this as DBI.

Is it DBIx::Class? If so I think you might want to look at:
 http://www.**--****.com/ ~ribasushi/DBIx-Class-0.08099_07/lib/DBIx/Class/Manual/Cookbook.pod#Using_database_functions_or_stored_procedures


Similar Threads:

1.Translate standard date/time format to EPOCH seconds

Good morning All.

I am looking for a way to translate date/timestamps in various formats to EPOCH seconds without using a module (cannot use a module in this situation other then the standard modules installed with Perl).

I am going to create a subroutine and parse the date/timestamp passed into it's various components. I will then pass those components to the timelocal function to return the EPOCH seconds.


$dayOfWeek, $day, $month, $hour, $min, $sec, $year=split /\s+|:/, $_[0];
my %months=qw(Jan 0 Feb 1 Mar 2 Apr 3 May 4 Jun 5 Jul 6 Aug 7 Sep 8 Oct 9 Nov 10 Dec 11);

The problem is, the date/timestamp passed can be of various formats:
Tuesday, 04/26/2005 06:02:43 PM
Tuesday, 26/04/2005 18:02:43
04/26/2005 06:02:43 PM
26/04/2005 06:02:43 PM
04/26/2005 18:02:43


I am looking for a way to gracefully handle the format using a format parameter passed with the date/timstamp to the subroutine.

Ex. my $epoch=convert_to_epoch("Tuesday, 26/04/2005 18:02:43","Day, DD/MM/YYYY HH24:MM:SS")
my $epoch=convert_to_epoch("04/26/2005 06:02:43 PM" ,"DD/MM/YYY HH12:MM:SS")

Is there a gracefull way of doing this WITHOUT using if if/elsif loop to parse every possible format type?

Again, I cannot use a module in this situation due to limitations of the runtime environment.


2.howto Date Time string from MYSQL to German Format

I want to format  DateTime string which looks like "2007-10-12
14:00:00" to German Format like "Montag, den 12. Oktober 2007"

This code (1) does not work:
$german_date = ParseDate('2007-10-12 14:00:00');
printf ("Minverbraucht  : %s\n",Date_to_Text_Long($german_date));

this code (2) works, because @anliefertag is already formatted as
printf ("Anliefertag  : %s\n",Date_to_Text_Long(@anliefertag))

any solutions from you how to format dateTime in example (1)?

tnx in advance

3.Can I set date format / style on DBI ADO connection

Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

        I am using DBI ADO   connection to access MS SQL Server. I switched from ODBC to ADO   recently.
              ADO connection is   returning the date format as mm/dd/yy hh:mi:ss, where as ODBC connection returns   datetime in yyyy-mm-dd hh:mi:ss(24hr) format.
              A lot of code is built   to handle the dates returned from queries in ODBC canonical   format.
  I can change all the queries using   CONVERT function to return in ODBC canonical format, instead of doing that for   all queries, is there anyway I can specify a date time format / style for ADO   Connection, so that all the dates returned by queries will be in the format   specified.
  Kumar   Talinki


4.Date Format for INFORMIX using DBI


I am using the DBI to extract some data from the underlying Informix
Database. Even after doing the following when I am printing the value
for $start_date, it is in the format 'dd/mm/yyyy' instead of the
expected 'yyyy/mm/dd'. Can any of you advise as to why this is

$sth = $dbh->prepare("select company,customer, pur_order, ref,
start_date, end_date, limit, on_billing, page_break, cons_usage,
cons_items, memo, pref_date, first_bill, igpm_adv,comment1, comment2,
comment3 from purchase_order");

($rv = $sth->execute);
$sth->bind_col(5, \$start_date, { TYPE => DBI::SQL_DATE });
 while (($company,$customer, $pur_order, $ref,$limit, $on_billing,
$page_break, $cons_usage, $cons_items, $memo,  $first_bill,
$igpm_adv,$comment1, $comment2, $comment3 ) = $sth->fetchrow)
	print "\nStart Date is : ${start_date}\n" ;

Nitin Nair

5.DBI, MS Access, inserting/updating a Access Date/Time value


Does anyone have any idea what is up with Microsoft Access and its 
ridiculous Date/Time formatting options? It doesn't seem to matter what 
format I attempt to insert with. The directory and db file permissions 
are all Full Control. I'm using the "#" around the Date/Time format as 
required. The database has nothing as the Format in Design view on the 
General tab. The insert below has never worked once. On the up side if I 
run an insert that has no mention of the Date/Time field then it will 
just insert the record using the =Now() function as the default value 
but that doesn't help much considering I would want to update the field. 
There are no question marks in any of the field names as suggested on 
many pages dicussing this error below. Thanks for any help!  I'm using 
MS Access, Perl, DBI & DBD::ODBC win32 modules, IIS on XP.

Here is the error:
DBD::ODBC::st execute failed: [Microsoft][ODBC Microsoft Access 
Driver]COUNT field incorrect  (SQL-07002)(DBD: st_execute/SQLExecute 
err=-1) at C:\Inetpub\Scripts\somedir\ line 18.

Here is the code:
#print "Content-type: text/html\n\n";
use DBI;
#         2005-04-12 20:48:31
#         4/5/2004 5:3:45 PM
#         4/12/2005 10:28:14 PM
#         d m yyyy h:m:s AM/PM

$dt = "#4/5/2004 5:3:45 PM#";

$sql = "INSERT INTO tblTest (TimeStamp) VALUES ($dt)";

my $dbh = DBI->connect("dbi:ODBC:dsntest", "", "", { PrintError => 0, 
RaiseError => 1 })
          || die "database connection can't connect to database 

my $sth = $dbh->prepare($sql) ||  die "prepare statement- $DBI::errstr";
$sth->execute() || die "execute statement- $DBI::errstr";

6. Help w/Class::Date Date.xs

7. Date::Calc and postgres timestamp

8. Class::DBI vs DBIx::Class

Return to PERL


Who is online

Users browsing this forum: No registered users and 47 guest