Normally, the best thing to do would be to work directly with the database's date/time functions.

Perl's DateTime's "now" feature is in this format

Not exactly, that's just the default stringification. Note that DateTime objects can be compared directly, they overload the comparison operators.

If you're really stuck with parsing a string, my personal favorite module is DateTime::Format::Strptime. Here's one way to go about it:

use warnings;
use strict;
use DateTime;
use DateTime::Format::Strptime;

my $strp = DateTime::Format::Strptime->new(on_error=>'croak',
    pattern => '%a %b %d, %Y - %H:%M:%S', time_zone => 'UTC');

my $dt = $strp->parse_datetime('Tue Oct 23, 2018 - 19:57:12');

print $dt->strftime('%Y-%m-%d %H:%M:%S %z'), "\n";

my $dt_one_day_ago = DateTime->now->subtract(days=>1);

print "$dt is ", $dt < $dt_one_day_ago ? '<' : '>=',
    " $dt_one_day_ago\n";
[download]

The above takes the time of day into account as well, if that's what you want. Also, make sure to set the time zones correctly!

(BTW, there is also DateTime::Format::MySQL and DateTime::Format::DBI, although I haven't used these myself.)

SELECT TIMESTAMPDIFF(
    MINUTE,
    STR_TO_DATE('Tue Oct 23, 2018 - 19:57:12','%a %b %d, %Y - %T'),
    NOW()
);
[download]

SELECT
    column_name,
    TIMESTAMPDIFF(
        SECOND,
        STR_TO_DATE(column_name,'%a %b %d, %Y - %T'),
        NOW()
    )
FROM table;
[download]

Hi, I wonder how your MySQL timestamp has that format? By default MySQL timestamps look like '2018-10-24 17:08:00'.

As ++haukex suggested, the best thing to do is let the DB do that query: after all, that's what it's for. You should easily be able to create an SQL query to fetch the records you're interested in with the DBI.

For comparison, here's the database table I am working on right now:

[localhost]:mysql> explain account;
+---------------+------------------+------+-----+-------------------+-
+----------------------------+
| Field         | Type             | Null | Key | Default           | 
+Extra                       |
+---------------+------------------+------+-----+-------------------+-
+----------------------------+

[redacted]

| created_on    | timestamp        | NO   |     | CURRENT_TIMESTAMP | 
+                            |
| modified_on   | timestamp        | NO   |     | CURRENT_TIMESTAMP | 
+on update CURRENT_TIMESTAMP |
+---------------+------------------+------+-----+-------------------+-
+----------------------------+
9 rows in set (0.00 sec)

[localhost]:mysql> select count(account_id) from account;
+-------------------+
| count(account_id) |
+-------------------+
|                66 |
+-------------------+
1 row in set (0.00 sec)

[localhost]:mysql> select count(account_id) from account where datedif
+f(now(), modified_on) >= 1;
+-------------------+
| count(account_id) |
+-------------------+
|                42 |
+-------------------+
1 row in set (0.00 sec)
[download]

"I'm trying to tell if a transaction occurred more than 1 day ago."

Note that the granularity of date_diff is one day, so for your query you probably want everything that's not 0 days difference.

Hope this helps!