Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2003
    Posts
    5

    Unanswered: Generating Dates in Perl and Cocatenating with Database Field

    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
    Last edited by rkumar28; 10-05-06 at 00:33. Reason: Spelling correction

  2. #2
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    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
    Last edited by Peter.Vanroose; 10-05-06 at 11:59.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  3. #3
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    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.

  4. #4
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    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.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  5. #5
    Join Date
    Feb 2006
    Posts
    56
    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.

  6. #6
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    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!
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  7. #7
    Join Date
    Dec 2006
    Posts
    6
    Along the lines of using perl modules to handle the dates, take a look at
    Code:
    DateTime
    and/or
    Code:
    DateTime::Set
    . Those modules should work nicely for this.

    Jim

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •