[rt-users] Searching and comparing two date field values instead of value with supplied valid

[Landon]

On 12 August 2013 07:38, Kevin Falcone <falcone at bestpractical.com> wrote:

> On Sun, Aug 11, 2013 at 09:23:32AM +0200, Joop wrote:
> >    Landon wrote:
> >
> >    Sorry for hijacking your question/thread.
> >
> >      Is there any way to search for tickets while comparing two field
> values?
> >      This doesn't work:
> >      Queue = 'Incident Reports' AND Created < CF.{SpecialDate} AND
> Status = 'new'
> >      It doesn't like the string "CF.{SpecialDate}" because it's not
> enclosed in quotations and
> >      therefor not a string.  Which is what I want to do but cannot I
> guess.  The error displayed
> >      on the RT UI is:
> >
> >        Wrong query, expecting a VALUE in 'Queue = 'Incident Reports' AND
> Created <
> >        >CF.{SpecialDate}<--here AND Status = 'new''
> >
> >    I'm looking at something similar. I would like to check whether the
> ResolvedDate is past the
> >    DueDate but that is also not possible. I seem to remember that this
> has been asked before but
> >    can't seem to find that thread anymore. Probably searching with the
> wrong keywords so if
> >    someone can point me in the right direction, please?
>
> Unfortunately, RT's TicketSQL cannot compare two fields like that.
> It's hard to free up the engineering time required to make it go
> right now.  Hopefully at some point it'll be part of a customer
> requirement and that will speed up implementation.
>
>
OK failing this then perhaps some light can be shed on comparing dates in
scrips/modules.  What I envision is an action module that goes through each
ticket from this condition:
--search RT::Search::FromSQL "id < 1000000"

The --action being something like "RT::Action::CreationDates" or something
that basically loads the ticket and compares the dates and closes tickets
whos CF.{ServerCreated} < Created.  This way any ticket containing an IP
address of a server that was created after the ticket was created can be
marked resolved.

The help I need though is on comparing dates using RT::Date or something.
 If I load the CF.{ServerCreated} and the Created value how do I compare
them reliably in modules?

Would this work?
...
my $ServerCreated = DateTime::Format::MySQL->parse_datetime(
$ticket->FirstCustomFieldValue('ServerCreated') );
my $Created = DateTime::Format::MySQL->parse_datetime( $ticketObj->Created
);
$ticket->SetStatus( 'resolved' ) if $ServerCreated < $Created;
…

-- 
Landon Stewart <LandonStewart at Gmail.com>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20130812/30e183df/attachment.htm>