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

i got a perl script. its over 800 lines.. i dont want to post the whole as most it is not relevant.. the script loops through different data files and input data into a mysql table.

the update when price/stock conditions code , doesn't work as intended as it doesnt take into considertion when the last time data was updated.

I never wrote the script but took a while to figure why it doesnt work.

the prodcut table in dabatse has a field called products_last_modified where date is stored in this format 2015-05-20 03:52:13 .

I need the script to update price/ stock if the products_last_modified data stamp is less then 12 hours.

i am note sure how best to do this.

The code is as 

 my $product_id;
      my $sth = $dbh->prepare("SELECT products_id, products_quantity, 
+products_weight, products_price, vendors_id FROM products WHERE produ
+cts.products_model = ".$dbh->quote($data->{'manufacturer_model'}));
      $sth->execute();
      if (my $ref = $sth->fetchrow_hashref()) {
        $product_id = $ref->{'products_id'};
        my $product_price = $ref->{'products_price'};
        my $product_quantity = $ref->{'products_quantity'};
        my $product_weight = $ref->{'products_weight'};
        
        my $sths = $dbh->prepare("SELECT products_id FROM specials WHE
+RE specials.products_id = ".$product_id);
        $sths->execute();

        # don't update if product is in specials
        unless (my $id = ($sths->fetchrow_array)[0]) {
          if ($ref->{'vendors_id'} == 1) {
            $dbh->do("UPDATE products SET products.vendors_id = ".$dat
+a->{'vendor_id'}." WHERE products.products_id = ".$product_id);
          }
          # update when price/stock conditions met
          if (($data->{'price'} <= $product_price && $data->{'quantity
+'} > 0).
          || ($product_quantity == 0 && $data->{'quantity'} > 0)) {
            $dbh->do("UPDATE products 
              SET products.products_price = ".$data->{'price'}.", prod
+ucts.products_quantity = ".$data->{'quantity'}.", 
              products.vendors_id = ".$data->{'vendor_id'}.", products
+.products_availability = ".$data->{'availability'}." 
              WHERE products.products_id = ".$product_id);
          }
        }

[download]

Replies are listed 'Best First'.
Re: need help with comparing time..
by choroba (Cardinal) on May 20, 2015 at 17:36 UTC
    I don't see how the code you posted is relevant. You can use arithmetic on seconds if you don't care about timezones, daylight saving times, leap seconds etc. 
    #! /usr/bin/perl
use warnings;
use strict;

use Time::Piece;


my $last_modified = 'Time::Piece'->strptime('2015-05-20 03:52:13', '%Y
+-%m-%d %H:%M:%S');
my $diff = -($last_modified - localtime) / 60 / 60;
print $diff < 12 ? 'less' : 'more (or equal)', " than 12 hours\n";

    [download]

    See also DateTime and Date::Manip.

    لսႽ† ᥲᥒ⚪⟊Ⴙᘓᖇ Ꮅᘓᖇ⎱ Ⴙᥲ𝇋ƙᘓᖇ
[reply]
[d/l]
Re: need help with comparing time..
by RedElk (Hermit) on May 21, 2015 at 14:53 UTC

    Like Choroba has already pointed out, use a module to handle the date/time calculations for you. Use that module to evaluate the products_last_modified value, something he also pointed out. Then, it looks like you could update your if statement to include another clause, something along the lines of:

    # update when price/stock conditions met
if (($data->{'price'} <= $product_price && $data->{'quantity'} > 0).
|| ($product_quantity == 0 && $data->{'quantity'} > 0) 
|| ($products_last_modified <= 12))

    [download]

[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom