Perl Date Comparison with MySQL

Question

I am trying to figure out how to compare MySQL dates with the current system time using perl.

I have a script that runs on a cron job that will send a notification if the current system date / time has passed the date / time of the returned record:

The application displays a table view:

EventId    Device        Location

CC: 123    something     BFE
TT: 456    anotherthing  BFE

How the script works, it finds the values ​​in the EventID field, analyzes the type (CC :, TT :, etc.) from the identifier (numerical part). An identifier is a unique identifier in another database / table that contains a finite time field. EventID itself is not unique and may contain duplicate elements in a table. Subprograms for each "type" exist, since each type has a different database and / or table.

The goal is that the script runs every minute and toggles the value of Expired. It is possible that something will expire, change, and then will not.

The script works fine for 1 problem, which seems to be related to time zones based on the feedback I received here. If I do not intentionally set the time zone for "America / New_York" ( $now) for the current system time, it will turn off for several hours ( $notz). so I need to find a way to make the date returned from MySQL exactly compared to the current system time.

$now->set_time_zone('America/New_York') doesn't work either.

I'm not sure how to do this, or even if the code I still have is the best approach (still pretty new to Perl):

#!/usr/bin/perl
use DBI;
use DateTime;
use DateTime::Format::MySQL;
use Switch;
my $now         = DateTime->now(time_zone => 'America/New_York');
my $notz        = DateTime->now();
my $maindb      = DBI=>connect(database);
my $seteventsql = qq { select * from view where EventId like 'IE:' or EventId like 'TT:' or EventId like 'CC:';};
my $commit      = $livedb->prepare($seteventsql);
$commit->execute() || die "could not set event: $DBI::errstr";

while(@events = $commit->fetchrow_array()) {
                (my $type, my $id) = split (/ /,$events[0]);
                $id =~ s|\D||g;
                switch ($type) {
                        case ('CC:') {check_expired_case($id);}
                        case ('TT:') {check_expired_task($id);}
                        case ('IE:') {check_expired_event($id);}
                }
}

sub check_expired_case {  
        my $id = shift; #id = 123
        my $sql = qq { select id, status_id, item_enddate from item where id = ?; };
        my $exec = $itemdb->prepare($sql);
        $exec->execute($id);
        while(my @row = $exec->fetchrow_array()) {
                my $status = $row[1];
                my $end = DateTime::Format::MySQL->parse_datetime($row[2]);
                if ($now > $end || $status ne 3 || $status ne 6) {
                        $sql = qq { update item set Expired = 1 where EventId = '$eventid';};
                        $maindb->do($sql)
                }else{
                        $sql = qq { update item set Expired = 0 where EventId = '$eventid';};
                        $maindb->do($sql)
                }
        }
        $exec->finish();
}


NoTZ: 2010-09-10T01:27:19 
Now:  2010-09-09T21:27:19
End:  2010-09-10T17:00:00

Thanks in advance. I hope I explained it well enough, it’s hard to explain how everything relates.

Answer 1

, , . DateTime::Format::*->parse_datetime ( ) ( UTC), , 5 .

Answer 2

if (DateTime->compare( $now, $end ) == 1) {  
  # do something
} else {  
  # do something else
}  

Answer 3

, DateTime UTC, .

, , - > DateTimes. DateTime->compare($now, $end) ?

Answer 4

. , . , , , (, "id" UNIQUE, ). :

sub set_expired {
  my $id = shift;
  my $dbh = DBI->connect(database);
  my $sql = qq{UPDATE table SET expired=IF(NOW() > date, 1, 0) WHERE id = ?};
  my $sth = $dbh->prepare($sql);
  $sth->execute($id);
  my $rows_affected = $sth->rows();
  # if no matches, $rows_affected will be 0; on error, -1
  $sth->finish();
}

mysqld, , table.date, , mysqld. , table.date /-, , mysqld . NOW() , ​​ , .

, , , , :

  ... SET expired=IF(NOW() > DATE_ADD(date, INTERVAL 4 HOUR), 1, 0) WHERE ...

, , table.date, ( ) : , , , , , . , GMT/UTC, .

, id UNIQUE-ness. WHERE 1 , . 1, table.date, , , . , n , , n . 1, 0 , . , n 1 0 , , .

, UPDATE, , , , . , ; , :)

Answer 5

lt gt perl:

my $isLessThan = '0001-01-01' lt '2050-01-01'; # Returns 1.
my $isBiggerThan = '0001-01-01' gt '2050-01-01'; # Returns ''.

. .

Answer 6

, . , , epoch() .