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

I am trying to sort timestamp. Timestamp = trunc of sysdate from an oracle DB. 
    while ( ($timestamp, $report_type, $data_name, $data_value) = $ora
+_sth->fetchrow_array)
{
        $key=$timestamp . "," . $report_type;
        if ($data_name eq "minutes") {$data_value = $data_value * 1440
+};
        if ($data_name eq "minutes") {$data_value = sprintf("%9.2f",$d
+ata_value)};
        $timestamps{$timestamp}=1;
        push @reporttypes, $report_type;
        $cellvalue{$key} = $data_value;
        }
  foreach $headingkey (@headings)
        {
        $row = $row . $query->th($headingkey);
        }
        print $query->Tr($row);

        foreach $timestamp (sort keys %timestamps)
        {
                $row = $query->td($timestamp);
                foreach $headingkey (@headings)
                {
                if ($headingkey eq "Timestamp") {next;}
                $key=$timestamp . "," . $headingkey;
                $row = $row . $query->td($cellvalue{$key});
                }
                print $query->Tr($row);
}

[download]
tia,

Replies are listed 'Best First'.
Re: Time sorting please help
by grinder (Bishop) on Jul 18, 2001 at 01:34 UTC

    The excellent formatting of the code doesn't make it easy to follow what's going on, but taking a wild guess, I would assume that timestamps are numeric, in which case you want to say sort {$a <=> $b} keys %timestamps

    But that only begs the question, why don't you get Oracle to sort the data for you with an ORDER BY clause?

    Something else to be said in passing: the normalisation of $date_value is better written as

      if ($data_name eq 'minutes') { $data_value = sprintf( '%9.2f', $data
+_value * 1440 ) };
  # or better yet
  $data_value = sprintf( '%9.2f', $data_value * 1440 ) if $data_name e
+q 'minutes';

    [download]
    --
    g r i n d e r
[reply]
[d/l]
[select]
Re: Time sorting please help
by malloc (Pilgrim) on Jul 18, 2001 at 01:30 UTC
    Sir, i am not sure what you are exactly asking, but wouldn't it be simpler to select the date in a format that would be easier to sort? Just specify a date mask when selecting it from Oracle. Something like
    select to_char(date_col,'YYYYMMDDHH24MISS') from table;

    [download]


    Should make it much easier! I hope this helps you out,
    -malloc
[reply]
[d/l]
      For display purpose (Web Reporting) i then have to reformat it for display. i was hoping to sort the hash in some way rather than changing the format of the data. People get pick about what they are used to seeing.
[reply]
Re: Time sorting please help
by voyager (Friar) on Jul 18, 2001 at 01:26 UTC
    I think I figured out what you're trying to do, but it wasn't easy :) ... the indentation doesn't seem to match the actual code structure.

    Is the sorting the only things that doesn't work? What to the values look like? Are they time of day? If so, how do you deal with different days, am/pm, etc. Showing some data in/out would help.

[reply]
      the results look like: 
      01-APR-01            
01-JUL-01 18286  5.46 0.96    17504.48 18286 22610.58 4190 
01-JUN-01 27934  5.75 1.04    28809.37 27934 35830.37 6334 
01-MAY-01            
02-JUL-01 20979  6.14 0.95    19860.98 20979 27195.53 4495 
02-JUN-01 24223  5.52 0.98    23598.10 24223 30321.18 5560 
03-JUL-01 20851  5.81 0.93    19363.53 20851 26090.85 4585 
03-JUN-01 23594  5.36 0.96    22680.82 23594 27655.15 5213 
04-JUL-01 16919  5.80 0.99    16727.62 16919 21459.60 3759

      [download]
      That is with a order by in the SQL statement as well.
[reply]
[d/l]
        It appears to be doing what you told it to do, not what you want it to do. :)

        I think you did the trunc, which probably made it a string, then sorted. If you look at the results, they are sorted as if the timestamp is a string.

        Why not sort on the whole (date/time) thing, which Oracle will do correctly, then in your Perl code do the truncating/formatting.

[reply]

Back to Seekers of Perl Wisdom