Perl DBI Loss of Date Precision

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

I have an oracle table with a column of the DATE datatype. There are values in there with the precision of a second... i.e. 20070601 16:30:23 When selecting from that table via perl dbi, I get '01-JUN-07' and the precision of the time has been lost. Since the dbi does not have the concept of a "session" I cannot issue the
$dbh->do(qq{ alter session set nls_date_format='YYYYMMDD HH24:MI:SS' });
Does anyone know how to select from a date column via dbi and preserve the time precision? Thanks in advance.

Comment on Perl DBI Loss of Date Precision Download Code

Replies are listed 'Best First'.
Re: Perl DBI Loss of Date Precision by Fletch (Bishop) on Jun 27, 2007 at 12:49 UTC
The DBD::Oracle documentation shows sample conversion routines. There's probably also a way (using something similar to those) to pull the date out as seconds since the epoch (i.e. a `time_t` value) which would probably be more suitable if you need to manipulate the values further.	[reply]
Re: Perl DBI Loss of Date Precision by mje (Curate) on Jun 27, 2007 at 13:51 UTC
You can certainly "do" an alter session to change the nls_date_format and it sticks with the $dbh you ran it on. Here is an example but using nls_timestamp_format: `perl -e 'use DBI;$h = DBI->connect("dbi:Oracle:XE","xxx","yyy"); $s = $h->prepare("select sys_extract_utc(systimestamp) from dual"); $s->execute; DBI::dump_results($s); $h->do("alter session set nls_timestamp_format=\"YYYYMMDD HH24:MI:SS\" +"); $s = $h->prepare("select sys_extract_utc(systimestamp) from dual"); $s->execute;DBI::dump_results($s);'` [download] produces: `"27-JUN-07 01.48.13.876853000 PM" 1 rows "20070627 13:48:13" 1 rows` [download]	[reply] [d/l] [select]
Re: Perl DBI Loss of Date Precision by maletin (Sexton) on Jun 27, 2007 at 13:22 UTC
maybe you want to set the environment: `export NLS_DATE_FORMAT='yyyymmdd hh24:mi:ss'` the same for the apache webserver: `PerlSetEnv NLS_DATE_FORMAT 'yyyymmdd hh24:mi:ss'`	[reply] [d/l] [select]