Question about perl & oracle & and a SQL query

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

Hi there! Im triying to execute the next query using perl:

my $sql1_datos = qq(select contador, cod_entidad_oim, cod_agr, cod_ope
+racion from mapcom_portext.TR0685002  where fch_hor_operacion between
+ to_date(\'$now\','dd-mon-yyyy') and to_date(\'$1hourago\','dd-mon-yy
+yy'));
[download]

But i dont know how i can use this time module:

use POSIX 'strftime';
$now = lc strftime("%d/%m/%y %H:%M:%S", localtime);
[download]

I need to do always a query where $now its equal to the current time in format DD/MM/YYYY HH24:MM:SS... and $1hourago must be $now - 1 hour.... This its where i dont know how i can tell perl to from the (for example) $now = 09/01/2009 15:00:00 rest 1 hour and use its as $1hourago.... Any ideas? maybe transforming it to time var, resting 3600 seconds and convert it again to the desiree format? Thanks! :::When you dream, there're no rules, ppl can fly...anything can happen!!!:::

Comment on Question about perl & oracle & and a SQL query Select or Download Code

Replies are listed 'Best First'.
Re: Question about perl & oracle & and a SQL query by 1Nf3 (Pilgrim) on Feb 09, 2009 at 14:18 UTC
`localtime()` can be used with arguments. When all that you want is the time from 1 hour ago, `localtime(time - 3600)` should work just fine. Assuming that your code is working, you could try the following: `use POSIX 'strftime'; $now = lc strftime("%d/%m/%y %H:%M:%S", localtime); $hourago = lc strftime("%d/%m/%y %H:%M:%S", localtime(time - 3600) );` [download] Regards, Luke	[reply] [d/l] [select]
Re^2: Question about perl & oracle & and a SQL query by Sombrerero_loco (Beadle) on Feb 09, 2009 at 14:23 UTC
Thanks!! its just works! :::When you dream, there're no rules, ppl can fly...anything can happen!!!:::	[reply]
Re: Question about perl & oracle & and a SQL query by roboticus (Chancellor) on Feb 09, 2009 at 14:22 UTC
Crazy Hat: I'd imagine that oracle has the getdate() function, as well as dateadd(), so you might just remove perl from the equation and use something like (untested): `my $sql1_datos = qq( select contador, cod_entidad_oim, cod_agr, cod_operacion from mapcom_portext.TR0685002 where fch_hor_operacion between getdate() and dateadd(h,-1,getdate) );` [download] ...roboticus	[reply] [d/l]
Re^2: Question about perl & oracle & and a SQL query by jhourcle (Prior) on Feb 09, 2009 at 18:18 UTC
You can do date arithmetic directly in Oracle, using days and fractional days. I don't deal with Oracle servers anymore, so this isn't tested, but the syntax should be: `select contador, cod_entidad_oim, cod_agr, cod_operacion from mapcom_portext.TR0685002 where fch_hor_operacion between sysdate() and (sysdate()-(1/24))` [download] You can also use the 'interval' syntax: `... between sysdate() and ( sysdate() - interval '1' hour )`	[reply] [d/l] [select]
Re^2: Question about perl & oracle & and a SQL query by Sombrerero_loco (Beadle) on Feb 09, 2009 at 14:25 UTC
Thanks too!! But i really dont know anything SQL in Oracle, and i prefeer to control how i run the query using perl (where i feel more skilled in it). But thanks anyway....another good response! :::When you dream, there're no rules, ppl can fly...anything can happen!!!:::	[reply]
Re^3: Question about perl & oracle & and a SQL query by massa (Hermit) on Feb 09, 2009 at 15:35 UTC
FWIW, it's preferable if you do `my $qry = qq(select contador, cod_entidad_oim, cod_agr, cod_operacio +n from mapcom_portext.TR0685002 where fch_hor_operacion between to_d +ate(?,'dd-mon-yyyy') and to_date(?,'dd-mon-yyyy'));` [download] and later `my $sth = $dbh->prepare($qry); $sth->execute($now, $onehourago);` [download] ... etc. (just in case)... []s, HTH, Massa (κς,πμ,πλ)	[reply] [d/l] [select]
Re^4: Question about perl & oracle & and a SQL query by Sombrerero_loco (Beadle) on Feb 10, 2009 at 14:57 UTC
Re^3: Question about perl & oracle & and a SQL query by maxodyne (Initiate) on Mar 03, 2011 at 16:35 UTC
If you want to get a `now()` out of Oracle, the only thing you need to know is to include the Oracle reserved word `sysdate` anywhere in your select statement.	[reply] [d/l] [select]
Re: Question about perl & oracle & and a SQL query by ww (Archbishop) on Feb 09, 2009 at 14:20 UTC
So try dreaming about the documentation, including `perldoc POSIX`.	[reply] [d/l]


more useful options
	PerlMonks