dbi fetchrow_array to access a timestamp

thelma1944 has asked for the wisdom of the Perl Monks concerning the following question:

Does anyone know how to get the time portion using dbi's fetchrow_array to access a timestamp's time value? The command is:

   
$hub_sql_cmd = "select DISTINCT $hub_stuff[$c][1] from
 $hub_stuff[$c][0]" ;

          $temp = $hub_sql_cmd . " where $hub_stuff[$c][1] = '$line2[$
+c]'";

 $hub_sth = $hub_dbh->prepare($temp)
         or die "Couldn\'t prepare statement: " .  
                     $hub_dbh->errstr;

$hub_sth ->execute || die "Couldn\'t execute statement";
          @line = $hub_sth->fetchrow_array;
[download]

which results in the sql command:

   
  select DISTINCT  AUDCTIME from mpi_audhead where AUDRECNO = 2.
[download]

AUDCTIME is a time stamp with a format of <date> <time> What the program NEEDs to get is the <time> value. When the perl code runs i get the <date> and that is all. The <time> is NOT brought into the array "@line".

Any meaningful suggestions?

You may send a reply to thelma1944@gmail.com

Thank you so very much!

-thelma

Comment on dbi fetchrow_array to access a timestamp Select or Download Code

Replies are listed 'Best First'.
Re: dbi fetchrow_array to access a timestamp by runrig (Abbot) on Aug 18, 2009 at 23:34 UTC
You don't say what database you're using, but if its Oracle, then the default datetime format is often 'DD-MON-YYYY'. I usually execute these statements after every connect: `$dbh->do(q(ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:S +S'); $dbh->do(q(ALTER SESSION SET NLS_TIMESTAMP_FORMAT='YYYY-MM-DD HH24 +:MI:SSxFF'); $dbh->do(q(ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT='YYYY-MM-DD H +H24:MI:SSxFF');` [download] That would get you the date and time. If you just want the time, select some function of the datetime that just extracts the time, or do a simple `s/.*\s//` or something on the datetime returned. Also, please wrap your code in <code></code> tags. And think about using placeholders and bind values in your SQL instead of injecting variables into your SQL. And no, I'm not going to email you.	[reply] [d/l] [select]
Re^2: dbi fetchrow_array to access a timestamp by kennethk (Abbot) on Aug 19, 2009 at 01:23 UTC
All this is documented in the CPAN entry for DBD::Oracle, in the other data types section. And as a side note, you might consider getting in the habit of using placeholders and bind values to avoid unpleasant surprises.	[reply]
Re: dbi fetchrow_array to access a timestamp by sanku (Beadle) on Aug 25, 2009 at 04:23 UTC
I think you might be defined the AUDCTIME column name data type as date so it will display only date to make it as date and time modify the AUDCTIME column data type as datetime	[reply]