If the table is in a SQL database, best would be to use some DBI module to access the database and get the date with a SELECT statement like

SELECT FROM table WHERE partnbr=? AND date<? ORDER BY DATE DESCENDING LIMIT 1

I'm no SQL expert, but maybe this will set you on the right track.

Kind of an illegitimate cousin of the schwartzian transformation.

This probably won't work as you expect. While it is definitly true interger indexes are always faster than strings, I imagine it would auctually slow things down as it applies to dates. This depends on his SQL engine though. If your using postgres your going to want to see ago for instance. SQL engines normally take dates in numerous string formats, output dates in numerous formats, and store them internally in what ever way is the most optimized - which is surely not string format.

8.5.1.4. Intervals interval values can be written with the following syntax: @ quantity unit quantity unit... direction Where: quantity is a number (possibly signed); unit is second, minute, hour, day, week, month, year, decade, century, millennium, or abbreviations or plurals of these units; direction can be ago or empty. The at sign (@) is optional noise. The amounts of different units are implicitly added up with appropriate sign accounting. Quantities of days, hours, minutes, and seconds can be specified without explicit unit markings. For example, '1 12:59:10' is read the same as '1 day 12 hours 59 min 10 sec'. The optional precision p should be between 0 and 6, and defaults to the precision of the input literal.

Which is all optimized by the engine.


Evan Carroll
www.EvanCarroll.com

If the data is not currently in a database and it is not possible to put it in one, then you should create a second hash keyed on the date:

It's not clear to me that you need to loop through the hash at all. What your line of code implies is that you have a HoHoH and that you are directly indexing to yet the value you want, which is about as efficient as you could want!

If you have code you are having trouble with in this regard, post an example showing the issue.

GrandFather, the problem seems to be this:

%partprice = (
  1 => {
    '2005-01-01' => 1.99, # new year special.
    '2005-01-02' => 8.99, # back to normal.
    '2005-02-15' => 9.99, # price bump.
    '2005-04-01' => 9.49, # on sale.
    '2005-04-08' => 9.99, # sale's over.
  },
);
[download]

The question from this is: what is the cost of item #1 for a purchase made on March 18th, 2005? For that, you have to scan through looking for the last date key that is less than or equal to the desired date.

If, however, you had an ordered hash, you could do a binary search for it. Or if you had a tree or something where the date keys were stored in an order, you could look it up faster.

That is exactly the problems. Thanks Tanktalus ;-)

my %partprice = ( PN01 => { 2005-01-01 => 29.95,
                            2005-01-05 => 20.00,
                            2005-01-19 => 29,95,
                            2005-03-01 => 33.50 },
                  PN02 => { ... },
                  ...
                 );

# pick a part number and a date of sale:
my $pn = 'PN01';
my $saledate = '2005-02-14';

my $price = getPrice( $saledate, $partprice{$pn} );

# do something with $price ...

sub getPrice
{
    my ( $sdate, $prices ) = @_;  # 2nd param is a hashref

    my @keydates = sort keys %$prices;

    while ( @keydates and $sdate lt $keydates[$#keydates] ) {
        pop @keydates;
    }
    # at this point, either the sale predates the price history (@keyd
+ates is empty)
    # or else the last element of @keydates points to the correct pric
+e for that date

    return ( @keydates ) ? $$prices{pop @keydates} : undef;
}
[download]

Okay... If you don't have it in a DB, store the dates ordered in an array and make a binary search for the correct date. That way you don't have O(n) but just O(log2(n))

Use the date as a hash key for finding the other data.

Here's a modified binary search that may help with the problem if there are lots of price changes per part:

use strict;
use warnings;

my %partprice =
  (
  1 =>
    {
    '2005-01-01' => 1.99, # new year special.
    '2005-01-02' => 8.99, # back to normal.
    '2005-02-15' => 9.99, # price bump.
    '2005-04-01' => 9.49, # on sale.
    '2005-04-08' => 10.99, # sale's over.
    },
  );

my %hPrices = %{$partprice{1}};
my @aPrices = map {"$_ $hPrices{$_}"} sort keys %hPrices;
print ((join "\n", @aPrices) . "\n");

my $date = '2005-01-00';
print "The price on the $date was " . FetchPrice (1, $date) . "\n";
$date = '2005-01-01';
print "The price on the $date was " . FetchPrice (1, $date) . "\n";
$date = '2005-01-02';
print "The price on the $date was " . FetchPrice (1, $date) . "\n";
$date = '2005-01-03';
print "The price on the $date was " . FetchPrice (1, $date) . "\n";
$date = '2005-02-16';
print "The price on the $date was " . FetchPrice (1, $date) . "\n";
$date = '2005-04-07';
print "The price on the $date was " . FetchPrice (1, $date) . "\n";
$date = '2005-04-08';
print "The price on the $date was " . FetchPrice (1, $date) . "\n";
$date = '2005-04-09';
print "The price on the $date was " . FetchPrice (1, $date) . "\n";


sub FetchPrice
{
my ($partnbr, $date) = @_;
my %hPrices = %{$partprice{$partnbr}};
my @aPrices = map {[$_, $hPrices{$_}]} sort keys %hPrices;

return $aPrices[0]->[1] if $date le $aPrices[0]->[0];
return $aPrices[-1]->[1] if $date ge $aPrices[-1]->[0];

my $posmin = 0;
my $posmax = $#aPrices;

while (1)
  {
  my $mid = int (($posmin + $posmax + 1) / 2);

  if ($aPrices[$mid]->[0] lt $date)
    {
    return $aPrices[$mid - 1]->[1] if $mid == $posmin;
    $posmin = $mid;
    }
  elsif ($aPrices[$mid]->[0] gt $date)
    {
    return $aPrices[$mid - 1]->[1] if $mid == $posmax;
    $posmax = $mid;
    }
  else
    {
    return $aPrices[$mid]->[1];
    }
  }
}
[download]

SELECT xxx_price
FROM price_history
WHERE product_id = <query-product-id>
AND <query-date> BETWEEN start_date AND end_date
[download]

The logical primary key will include the product id, start date and end date, although in practice an artificial primary key of e.g. entry_id may be used for performance purposes.