Perl Compare dates with MySQL

Question

I'm struggling to figure out how to compare MySQL dates with current system time using perl.

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

An application displays a table view:

EventId    Device        Location

CC: 123    something     BFE
TT: 456    anotherthing  BFE

How the script works is it finds values in the field EventID, parses the type (CC:, TT:, etc) from the ID (the numeric portion). The ID is a Unique ID in another database/table which contains an end time field. EventID itself is not Unique and may have duplicate items in the table. Sub routines 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 for something to expire, a change to be made and subsequently unexpire.

The script works fine save for 1 issue which appears to be related to timezones based off of the feed back I've recieved here so far. If I don't implicitly set the timezone to 'America/New_York'($now) for the current system time , it is off by a number of hours ($notz). therefore I need to find a way to make the date returned from MySQL be compared with the current system time accurately.

$now->set_time_zone('America/New_York') does not seem to work either.

I'm unsure of how to do this, or even if the code I have thus far is the best approach (Still fairly 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've explained this well enough, its hard difficult to explain how everything relates.

Answer 1

It's likely because those are returning string values. What you'll want to do is call the epoch() method on each of those dates, and compare that.

Answer 2

You'll have to dump the objects to confirm, but it sounds like you have a timezone issue. DateTime::Format::*->parse_datetime (or any other constructor) will use a floating timezone (roughly approximate to UTC) if you don't specify one, so your comparison will be off by 5 hours.

Answer 3

Try this

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

Answer 4

I'm with Ether here, because the DateTime documentation suggests converting everything immediately to UTC to avoid timezone issues.

If it's not a timezone issue, I'd wonder if there's just some ambiguity about using the > operator with DateTimes. Have you tried checking the result of DateTime->compare($now, $end) as suggested in the documentation?

Answer 5

You're going about this the wrong way. A relational database isn't a key-value store that you have to read and write one value at a time. You can do this, atomically, much faster, with less code and fewer dependencies (and, if the 'id' column is non-UNIQUE, without the bug). Try this:

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();
}

Your mysqld will hopefully be storing the table.date time values that are in the same timezone that mysqld itself is running in. It looks like your table.date values are in the America/New York timezone, so with a little luck, your mysqld is running with its timezone set the same way. In that happy case, NOW() will be a timestamp that can be directly compared to table.date, as above.

If you've been feeding it values from a different timezone, you will probably want to adjust the comparison, for example:

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

Things get tricky if the timezone that you're storing table.date values in varies (or has varied) with daylight savings: for example, there's a 1-hour period every fall where the same timestamp occurs twice and there's no way to tell by looking at the saved value which you mean. That's part of why it's generally better to store timestamps in GMT/UTC if you can.

Now, as to id's UNIQUE-ness. If the WHERE clause is guaranteed to match only 1 row, then the above code and yours will do the same thing. But if it can match more than 1, and if those matched rows can have differing table.date values, then your code probably has a bug. Namely, it will iterate through all n of the matched rows, and for each one, it will set all n rows to have a table.expired of either 1 or 0 depending on which row it's on. The result will be that all n rows will end up with a 1 or 0 depending on the last row returned, which isn't defined, so your result will be essentially random.

The one thing you don't get from the above UPDATE is a list of id's that were matched or changed, but it doesn't look like you needed that information anyway. If you do need it, there's a clever way to get it; respond and I'll share that :)