Custom sorting records by date (without SQL)

[shrubble]

I inherited a program, written in perl-script (.asp) that acts as a front-end to a MS Access database which stores our company statistics. The problem is that the original database's date fields are set as type "text" instead of type "date", so when a SQL query from the program calls items sorted by date, they are sorted alpha-numerically instead of chronologically. The dates are stored in the database as mm/dd/yyyy.

If I change the date fields in the database to type "date", the database queries correctly, but the front-end interface stops working! I am trying to avoid re-writing this thing, as it is rather large (although it would be considerably smaller if written correctly), so I would like advice on writing a sub function that would sort the returned record array by date (again, in the format of mm/dd/yyyy).

I do not know if there is a pre-existing function that will:
a) cast the record substring as a date, and
b) sort them by date

or if I need to write the algorithm from scratch. Any help would be much appreciated!

Thanks!

deletion mistake
no I can't recover that
you didn't save it

-Shrubble

http://www.ishrubble.com

 

[raider2001]

This is easy enough to do with sort. See the example below:

@list = ("01/22/2000", "01/21/2001", "08/08/2000", "12/12/2000", "09/09/1999");

sub mysort() {
    @data1 = split ("/",$a);
    @data2 = split ("/",$b);
    $data1[2] <=> $data2[2] || $data1[0] <=> $data2[0] || $data1[1] <=> $data2[1];
}

@list = sort mysort @list;
print &quot;@list\n&quot;;

 

[homefish]

Compare first the years and then, if the years are equal, look at the months. If the months are the same, look at the days. This gives rise to code like this:

        sub cmp_by_bits {
                my( $ad,$am,$ay,$bd,$bm,$by );
                $_[0] =~ /(\d+)\/(\d+)\/(\d+)/ # dd/mm/yy{yy}
                        && do { $ad = $1; $am = $2; $ay = $3 };
                $_[1] =~ /(\d+)\/(\d+)\/(\d+)/
                        && do { $bd = $1; $bm = $2; $by = $3 };
                return $ay==$by ?
                                ( $am==$bm ? $bd <=> $ad : $bm <=> $am )
                        : $by <=> $ay;
        };

This routine can be adapted easily for use as an argument to the

sort

function.

If, however, you assume (wrongly) that there are 32 days in every month and 16 months in every year, the number of days since AD0 is

      (((yyyy * 16) + mm) * 32) + dd.

This is obviously not true but there is a one-to-one mapping from the real number of days to this bogus number which preserves order, so it can be used as the basis of a sort algorithm. 16 and 32 were chosen as multipliers as they can be implemented using bitshifts to speed things up. This leads to code like this:

        sub cmp_by_bogus_num {
                my( $a,$b );
                $_[0] =~ /(\d+)\/(\d+)\/(\d+)/ # dd/mm/yy{yy}
                        && do { $a = ((($1<<4)+$2)<<5)+$3 };
                $_[0] =~ /(\d+)\/(\d+)\/(\d+)/
                        && do { $b = ((($1<<4)+$2)<<5)+$3 };
                return $a <=> $b;
        };

If you are absolutely confident about the input format (you say they're coming from a database) then you can use unpack rather than regex matching for a further speed improvement. I save a little more by concatenating the input dates and unpacking them both in one operation. Losing the temporary variables may not help but it "feels right".

        sub best_yet {
                my @d = unpack( 'A2AA2AA4A2AA2AA4', "$_[0]$_[1]" ; );
                #                dd/mm/yyDD/MM/YY
                return ((($d[0]<<4)+$d[2])<<5)+$d[4]
                   <=> ((($d[5]<<4)+$d[7])<<5)+$d[9]
        };

It's not particularly legible but it's fast. You can obviously use string concatenation and replace the last line with

                return &quot;$d[4]$d[2]$d[0]&quot; <=> &quot;$d[9]$d[7]$d[5]&quot;;

which forces a numeric comparison on the re-ordered strings but string concatenation is much slower than a bit-shift.


Have fun,

fish.

 

[shrubble]

Thanks! Let me see what happens...

deletion mistake
no I can't recover that
you didn't save it

-Shrubble

http://www.ishrubble.com

 

[icrf]

If speed is a serious concern (done very often or large data set) look into the packed default sort method. It's a variation on the Schwartzian Transform that utilizes the pack() builtin and the C-optimized lexical default method of sort(). It's very fast to run, and reasonably memory-friendly, too, all in 6-8 lines of code:

http://www.sysarch.com/perl/sort_paper.html

________________________________________
Andrew - Perl Monkey

 

[shrubble]

Alright guys, here's the thing, I should have led with this statement before:

I program mostly in C style languages (C++, C#, Java, etc...), and while I'm able to understand what I need to do, and I can program some basic Perl, many of these cryptic (appearing) functions go screaming over my head!

So please, bear with the newbie:

I need to search out the substring date field from the returned recordset and compare them in a mm/dd/yyyy format, many of the above functions are very close to that, but I can't easily discern what I need to change to make them exactly that.

Any help would be appreciated!


deletion mistake
no I can't recover that
you didn't save it

-Shrubble

http://www.ishrubble.com

 

[icrf]

Modules are available that can parse the date into a standard Unix timestamp (epoch seconds) which are easily compared. Here's how I'd do it:

use Date::Parse;
my @list = ("01/22/2000", "01/21/2001", "08/08/2000", "12/12/2000", "09/09/1999");

my $i = 0;
my @sorted = map $list[substr($_,4)],
             sort
             map pack('N',str2time($_)).$i++,
             @list;

"I need to search out the substring date field from the returned recordset and compare them..."

There are many ways to return data from a database, you have to tell us how your recordset is being represented in Perl. Array of arrayrefs (list of lists)? Array of hashrefs?

The examples above all manipulate Perl's sort() function, which takes an array of items to be sorted as an argument (after an optional custom sort sub), and returns an array of those items in sorted order. So you have to get your record set into an array somehow.

________________________________________
Andrew - Perl Monkey