[rt-users] search for tickets with Date or Told Not set

[Richard McMahon]

As suggested by Kevin I created an Issue in the RT issue system.

Following some dialogue within the issue system with
Ruslan Zakirov the following is a summary of the situation and
my proposed solution which might be of interest to others. I
would welcome comments and advice.

It looks like the Told time "incorrectly" IMHO has a default value of Null 
whereas the other times have a default time of 1970-01-01 00:00:00 which 
is Unix/Posix time = 0. This effects RT4 as well since the 'bug'
effects queries in RT on the Demo site.

There might be good reasons for the above feature.

I see two possible solutions:
(i) NULL support for Times in TicketSQL [mentioned by by Kevin Falcone]

This requires someone to do it and is not a breaking change.

(ii) Change the default value for Told time to be the same as
the other times. This might break existing scripts but is a short
term fix. It also makes all times the same.

I welcome opinions on this proposal below e.g. is it likely
to break other extensions e.g. SLA

Updating existing tickets
UPDATE tickets SET Told='1970-01-01 00:00:00' WHERE told is null;

This 'fixes' existing tickets.

This then changes the default in the Table:

ALTER TABLE Tickets COLUMN Told SET DEFAULT='1970-01-01 00:00:00'

Thanks Richard

On Fri, 9 Sep 2011, Kevin Falcone wrote:

> Date: Fri, 9 Sep 2011 11:21:34 -0400
> From: Kevin Falcone <falcone at bestpractical.com>
> Reply-To: rt-users at lists.bestpractical.com
> To: rt-users at lists.bestpractical.com
> Subject: Re: [rt-users] search for tickets with Date or Told Not set
> 
> On Fri, Sep 09, 2011 at 03:34:28PM +0100, Richard McMahon wrote:
>> Hello,
>>
>> I would like to search for tickets with Last Contact: Not
>> set. i.e Told = 'Not set' or Told IS NULL in the query builder.
>>
>> Neither of the above return any tickets. This is an issues in RT 3.8
>> and I can reproduce the issues with the demo sites that run 4.0 and
>> 4.2.
>>
>> The issue is part of specific query which is intended to check for
>> tickets which have not had requestor contact for a week.
>>
>> i.e. Told < '-7 days'
>>
>> The above does not return the ticket where there has been no
>> contact with the requestor by the owner since Last Contact: Not set
>>
>> I have searched this list, wiki and google without success.
>
> As I believe I mentioned on #rt when you asked this question, the
> TicketSQL parser only accepts a few operators on Date fields.
>
>    die "Invalid Date Op: $op"
>            unless $op =~ /^(=|>|<|>=|<=)$/;
>
> If you read your logs, you should be seeing:
> [Fri Sep  9 15:19:08 2011] [error]: Invalid Date Op: IS
>
> You can file a feature request in the bug tracker:
> http://bestpractical.com/rt/issues.html
>
> -kevin
>

-------------------------------------------------------------------
  Dr. Richard G. McMahon    | Phone (office)     44-(0)-1223-337519
  University of Cambridge   |       (switchboard)       1223-337548
  Institute of Astronomy    |       (secretary)         1223-337516
  Madingley Rd              | FAX                       1223-337523
  Cambridge, CB3 OHA, UK.   | mobile                    7885-409019
  Office: Hoyle 18          | home                      1223-359770
-------------------------------------------------------------------
  email: rgm at ast.cam.ac.uk  | WWW:    http://www.ast.cam.ac.uk/~rgm
  richardgmcmahon at gmail.com | skype:                richardgmcmahon
-------------------------------------------------------------------