Search for tickets with Date or Told Not set

RT Users
1

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.

Thanks Richard

2

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

3

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 RichardOn Fri, 9 Sep 2011, Kevin Falcone wrote:

Date: Fri, 9 Sep 2011 11:21:34 -0400
From: Kevin Falcone falcone@bestpractical.com
Reply-To: rt-users@lists.bestpractical.com
To: rt-users@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@ast.cam.ac.uk | WWW: Prof Richard McMahon, Institute of Astronomy, Cambridge Home Page
richardgmcmahon@gmail.com | skype: richardgmcmahon