Ticket SQL for dates?

Hello list,
In a thread a week or two ago, I was asking about the syntax for finding
tickets by relative dates, like “3 days ago”. It wasn’t working, and at
least one other list member was able to confirm that it wasn’t. I really
need this, or something like it, but no workaround I try does anything.

First, I took the straight MySQL route, doing advanced searches with
queries like

Owner = 'ahall’
and Status = 'active
and LastUpdated <= DATE_SUB(CURDATE(), INTERVAL 3 day)

But I got an error saying that a value was expected for CURDATE(). I tried
DATEDIFF, and a simple “LastUpdated <= (now() - INTERVAL 3 DAYS)”, all with
similar errors.

Hoping it was just something odd in the search interface, I tried this in
the cron tool, but I’m getting no tickets at all. Using the RT-recommended
syntax of “3 days ago” is convenient, but doesn’t work at all. It’s like it
gets ignored completely–tickets are found with the right status, owner,
etc, but never any date considerations whatsoever.

Is there anything else I can try in order to get tickets last updated N
days ago? I’ve tried UntouchedInHours, but it relies on this same “N hours
ago” syntax and so also fails. I find it hard to believe that 4.4.1 would
have been out for so long with such a critical bug, but last time I asked
about this, someone else on the list did say they could confirm the
problem. Thanks for any answers!

Alex Hall
Automatic Distributors, IT department
ahall@autodist.com

In a thread a week or two ago, I was asking about the syntax for finding
tickets by relative dates, like “3 days ago”. It wasn’t working, and at
least one other list member was able to confirm that it wasn’t.

You’re getting tripped up by the “ago”, which in English
inverts the meaning of less-than and greater-than. A search
for “LastUpdated > ‘3 days ago’” finds tickets whose LastUpdated stamp
is after the point in time 3 days ago – that is, within the last three
days. Here’s that search on issues.bestpractical.com:

https://issues.bestpractical.com/Search/Results.html?Query=Queue+%3D+'RT'+AND+Status+%3D+'__Active__'+AND+LastUpdated+>+'3+days+ago'

TicketSQL isn’t SQL because we don’t want to allow SQL injection
attacks.

  • Alex