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!!!:::

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]
      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]

      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]
      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]
        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]

        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]

Back to Seekers of Perl Wisdom