Databases
StackOverflow2013
Postsdbo
POPerl Compare dates with MySQL
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POPerl Compare dates with MySQL
    primarykey
    data
    text
    <p>I'm struggling to figure out how to compare MySQL dates with current system time using perl.</p> <p>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:</p> <p>An application displays a table view:</p> <pre><code>EventId Device Location CC: 123 something BFE TT: 456 anotherthing BFE </code></pre> <p>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.</p> <p>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.</p> <p>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'(<code>$now</code>) for the current system time , it is off by a number of hours (<code>$notz</code>). therefore I need to find a way to make the date returned from MySQL be compared with the current system time accurately.</p> <p><code>$now-&gt;set_time_zone('America/New_York')</code> does not seem to work either.</p> <p>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):</p> <pre><code>#!/usr/bin/perl use DBI; use DateTime; use DateTime::Format::MySQL; use Switch; my $now = DateTime-&gt;now(time_zone =&gt; 'America/New_York'); my $notz = DateTime-&gt;now(); my $maindb = DBI=&gt;connect(database); my $seteventsql = qq { select * from view where EventId like 'IE:' or EventId like 'TT:' or EventId like 'CC:';}; my $commit = $livedb-&gt;prepare($seteventsql); $commit-&gt;execute() || die "could not set event: $DBI::errstr"; while(@events = $commit-&gt;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-&gt;prepare($sql); $exec-&gt;execute($id); while(my @row = $exec-&gt;fetchrow_array()) { my $status = $row[1]; my $end = DateTime::Format::MySQL-&gt;parse_datetime($row[2]); if ($now &gt; $end || $status ne 3 || $status ne 6) { $sql = qq { update item set Expired = 1 where EventId = '$eventid';}; $maindb-&gt;do($sql) }else{ $sql = qq { update item set Expired = 0 where EventId = '$eventid';}; $maindb-&gt;do($sql) } } $exec-&gt;finish(); } NoTZ: 2010-09-10T01:27:19 Now: 2010-09-09T21:27:19 End: 2010-09-10T17:00:00 </code></pre> <p>Thanks in advance. I hope I've explained this well enough, its hard difficult to explain how everything relates.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USMose
    1. USMose
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POPerl Compare dates with MySQL
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POPerl Compare dates with MySQL
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POPerl Compare dates with MySQL
      1. This table or related slice is empty.
      1. VTUpMod
    1. COSo to clarify, your code is going to the "do something" path, not "do something else?
      singulars
      1. POPerl Compare dates with MySQL
      1. USEther
    2. COThe actual production script I have sets a value to a database of either 1 or 0 whether or not the current time is before or after the end time.
      singulars
      1. POPerl Compare dates with MySQL
      1. USMose
    3. CO@mose: I'm trying to confirm that your code incorrectly says that 2010-09-08T20:03:38 > 2010-09-10T17:00:00?
      singulars
      1. POPerl Compare dates with MySQL
      1. USEther
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload