Generating Dates in Perl and Cocatenating with Database Field

rkumar28 · #1 (**permalink**) 10-04-06, 23:31

Hi,
I am a newbie in perl. I will really appreciate any help and advice.

I have a Oracle table that contains quite a few Filenames. I am trying to write a perl script thats generates all the possible dates between two dates that is passed as a parameter to Perl script and concatenate these dates with FileNames.

For E.G.:
I grab one filename from Oracle table called "SMITH.sfs". I pass in two dates to a perl script 2006-06-01 and 2006-06-30. I have to generate the 30 dates one for each day between the above two dates that was passed in.
I am trying to get the output something like below and write it to a flat file:

SMITH.sfs.20060601
SMITH.sfs.20060602
SMITH.sfs.20060603
........and so on till
SMITH.sfs.20060630

Below is my script that works fine with the Oracle SYSDATE.....(This script is not passing the date right now). I have to modify this script to generate dates as mentioned above.

#!/usr/bin/perl
use DBI;
$dbh = DBI->connect("dbi:Oracle:sid...",user,passwd) ;
print "Connected Sucessfully to Oracle database \n"; \
$sql = q{
Select
ID,
File_Name||to_char(SYSDATE,'YYYYMMDD') As FILE_NAME
From TABLE A
Where D_Reg Like '%d{8}%'
UNION
Select
ID,
File_Name||to_char(SYSDATE,'YYYYMMDD') As FILE_NAME
From TABLE A
Where D_Reg Like '%d{6}%'
};
$stmt = $dbh->prepare($sql) or die "Cannot prepare the SQL";
$stmt->execute() or die "Cannot Excecute";

$rec = $stmt->fetchall_hashref('DATA_FL_ID');
for $DATA_FL_ID ( keys %$rec )
{
my $record = $rec->{ $DATA_FL_ID };
print $record->{'FILE_NAME'}, "\n"; -->THIS PRINTS THE FILENAMES....
}

I will really appreciate any help in this regards.

Thanks
RK

Peter.Vanroose · #2 (**permalink**) 10-05-06, 10:47

Just call the database repetitively (in a loop), asking it for 1 day added to the date you pass it. If I'm not mistaken, just add 1 to the original date field to do this in Oracle. End the loop when it returns the end date. (Beware of infinite loops!)

Alternatively (and a bit better in terms of performance) use recursive SQL.
I don't know whether Oracle supports this; the SQL-2 standard definitely does, and e.g. DB2 implements it.

The advantage of recursive SQL is that you pay only one connection to the database. The number of rows being returned is of course the same.

Code:

WITH dates(d)
AS
(
  SELECT cast('2006-06-01' AS Date)
 UNION ALL
  SELECT d + 1
  FROM   dates
  WHERE  d < cast('2006-06-30' AS date)
)
SELECT mytable.fname || '.' || CAST(dates.d AS char(10))
FROM   mytable CROSS JOIN dates

sco08y · #3 (**permalink**) 10-07-06, 19:55

Why are you asking the database to generate the dates?

I'm all in favor of having one copy of data with one set of integrity constraints in the DBMS. But if this isn't going to go into a stored procedure, do it in Perl.

Try this:

Code:

#use DBI and connection stuff
foreach my $name (@{$dbi->selectcol_arrayref(<<SQL)}) {
SELECT File_Name FROM A
SQL
   print "$name.$_\n" for 20060601..20060630;
}

Notes: selectcol_arrayref returns (as the name indicates) a *reference* to an array, so you dereference it. That's what @{} does. foreach then iterates through that array.

<<SQL is a here-doc type string. See perldoc perlop to find out more, search for "here-doc" in it.

The .. operator is another one to look up under perldoc perlop. Perl has a ton of time and date modules depending on how fancy you want to get. See perldoc Time::Local.

Peter.Vanroose · #4 (**permalink**) 10-09-06, 02:24

Quote:

Originally Posted by sco08y

20060601..20060630;

This solution will only work if you stay within one month.
But you are right when you say that the Time module may be better placed than the database to generate dates.

KevinADC · #5 (**permalink**) 10-09-06, 17:28

This is the perl solution I posted on another forum to this same question:

Code:

use POSIX qw(strftime);
use Time::Local 'timelocal_nocheck';

# assumes we have the filename already
my $file = 'SMITH.sfs';

# here are your two passed in arguments
my $start_date = '2006-06-01';
my $days_to_add = '30';

$start_date =~ /(\d+)-(\d+)-(\d+)/;
my ($mday,$mon,$year) = ($3,$2,$1);

# convert to epoch time
my $date = timelocal_nocheck(0,0,0,
                             $mday-1,
                             $mon-1,
                             $year
                            );
# make the new filenames
for (1..$days_to_add) {
   my $new_date = strftime('%Y%m%d',0,0,0,
                           (localtime($date+=86400))[3,4,5]
                          );
   print "$file.$new_date\n";
}

seems the person that asked the question accepted the above as a solution and never checked the other forums he posted the same question on.

Peter.Vanroose · #6 (**permalink**) 10-10-06, 02:07

The only advantage (I see) for doing the date arithmetic in SQL is the simpler syntax and (hence) a safer way to get it right.
(E.g., using timelocal, one often forgets to subtract 1 from day or month.)
On the other hand, of course, one is paying for 30 database accesses, which (depending on where the database resides) may be an expensive operation.
So I would only suggest the SQL approach if at the same time that date is used to extract some other information from the database.
And indeed, if e.g. one wants to perform an identical query parametrised by those 30 dates, doing the artihmetic in SQL allows using a single SQL query instead of 30 separate ones. Which may be almost 30x cheaper!