Using 'today' in an Advanced search gives different results than using explicit date

RT Users
#1

Hello,

We have a global custom field of Type ‘select date’ (not datetime)
called FollowUp.

When trying to query tickets using the Advanced tab and TicketSQL
using the word ‘today’ as described on this page,
http://requesttracker.wikia.com/wiki/TicketSQL, we are not getting the
same results as explicitly searching with today’s date.

For example I would expect these to return the same tickets but they don’t:

This returns 7 tickets.
Status != ‘resolved’ AND CF.{FollowUp} < ‘2012-08-25’

This returns 513.
Status != ‘resolved’ AND CF.{FollowUp} < ‘tomorrow’

We are using RT 4.0.5.

Suggestions welcomed.

Is there a better place to learn about TicketSQL?

Thank you
Robert

#2

For example I would expect these to return the same tickets but they don’t:

This returns 7 tickets.
Status != ‘resolved’ AND CF.{FollowUp} < ‘2012-08-25’

This returns 513.
Status != ‘resolved’ AND CF.{FollowUp} < ‘tomorrow’

Capture the SQL that gets run for those queries, and we can see the
difference between parsing. It shouldn’t be different, but I’m
guessing an implicit time part might be sneaking in.

#3

For example I would expect these to return the same tickets but they don’t:

This returns 7 tickets.
Status != ‘resolved’ AND CF.{FollowUp} < ‘2012-08-25’

This returns 513.
Status != ‘resolved’ AND CF.{FollowUp} < ‘tomorrow’

Capture the SQL that gets run for those queries, and we can see the
difference between parsing. It shouldn’t be different, but I’m
guessing an implicit time part might be sneaking in.

Here is the SQL and stack trace of two queries obtained by using
“Configuration » Tools » SQL Queries”.

SELECT COUNT(DISTINCT main.id) FROM Tickets main JOIN

ObjectCustomFieldValues ObjectCustomFieldValues_1 ON (
ObjectCustomFieldValues_1.CustomField = ‘49’ ) AND (
ObjectCustomFieldValues_1.ObjectType = ‘RT::Ticket’ ) AND (
ObjectCustomFieldValues_1.Disabled = ‘0’ ) AND (
ObjectCustomFieldValues_1.ObjectId = main.id ) LEFT JOIN
CustomFieldValues CustomFieldValues_2 ON (
CustomFieldValues_2.CustomField =
ObjectCustomFieldValues_1.CustomField ) AND ( CustomFieldValues_2.Name
= ObjectCustomFieldValues_1.Content ) WHERE (main.Status != ‘deleted’)
AND (main.Queue = ‘74’ AND ( ( ( ObjectCustomFieldValues_1.Content <
‘today’ OR ( ( ObjectCustomFieldValues_1.Content = ‘’ OR
ObjectCustomFieldValues_1.Content IS NULL ) AND
ObjectCustomFieldValues_1.LargeContent < ‘today’ ) ) ) ) ) AND
(main.Type = ‘ticket’) AND (main.EffectiveId = main.id) Toggle stack
trace

Stack:
[/opt/rt4/sbin/…/lib/RT/Handle.pm:1180]
[/usr/local/share/perl5/DBIx/SearchBuilder/Handle.pm:589]
[/usr/local/share/perl5/DBIx/SearchBuilder.pm:291]
[/opt/rt4/sbin/…/lib/RT/SearchBuilder.pm:331]
[/opt/rt4/sbin/…/lib/RT/Tickets.pm:2961]
[/usr/local/share/perl5/DBIx/SearchBuilder.pm:1461]
[/opt/rt4/sbin/…/lib/RT/Tickets.pm:2871]
[/opt/rt4/share/html/Search/Results.html:132]
[/opt/rt4/sbin/…/lib/RT/Interface/Web.pm:548]
[/opt/rt4/sbin/…/lib/RT/Interface/Web.pm:295]
[/opt/rt4/share/html/autohandler:53]

     SELECT COUNT(DISTINCT main.id) FROM Tickets main JOIN

ObjectCustomFieldValues ObjectCustomFieldValues_1 ON (
ObjectCustomFieldValues_1.CustomField = ‘49’ ) AND (
ObjectCustomFieldValues_1.ObjectType = ‘RT::Ticket’ ) AND (
ObjectCustomFieldValues_1.Disabled = ‘0’ ) AND (
ObjectCustomFieldValues_1.ObjectId = main.id ) LEFT JOIN
CustomFieldValues CustomFieldValues_2 ON (
CustomFieldValues_2.CustomField =
ObjectCustomFieldValues_1.CustomField ) AND ( CustomFieldValues_2.Name
= ObjectCustomFieldValues_1.Content ) WHERE (main.Status != ‘deleted’)
AND (main.Queue = ‘74’ AND ( ( ( ObjectCustomFieldValues_1.Content <
‘2012-08-27’ OR ( ( ObjectCustomFieldValues_1.Content = ‘’ OR
ObjectCustomFieldValues_1.Content IS NULL ) AND
ObjectCustomFieldValues_1.LargeContent < ‘2012-08-27’ ) ) ) ) ) AND
(main.Type = ‘ticket’) AND (main.EffectiveId = main.id) Toggle stack
trace

Stack:
[/opt/rt4/sbin/…/lib/RT/Handle.pm:1180]
[/usr/local/share/perl5/DBIx/SearchBuilder/Handle.pm:589]
[/usr/local/share/perl5/DBIx/SearchBuilder.pm:291]
[/opt/rt4/sbin/…/lib/RT/SearchBuilder.pm:331]
[/opt/rt4/sbin/…/lib/RT/Tickets.pm:2961]
[/usr/local/share/perl5/DBIx/SearchBuilder.pm:1461]
[/opt/rt4/sbin/…/lib/RT/Tickets.pm:2871]
[/opt/rt4/share/html/Search/Results.html:132]
[/opt/rt4/sbin/…/lib/RT/Interface/Web.pm:548]
[/opt/rt4/sbin/…/lib/RT/Interface/Web.pm:295]
[/opt/rt4/share/html/autohandler:53]

Thanks
Robert

#4

All,

I am trying to create a top closers list on the default dashboard. I have the exact SQL statment i want to use, but i am lost after that.

select u.Name as User, count(1) as TotalClosedTix from Users u join Tickets t on u.id = t.owner where t.Status = “resolved” group by u.Name order by 2 desc limit 10 \G;

*************************** 1. row ***************************
User: winnj
TotalClosedTix: 6
*************************** 2. row ***************************
User: dennisc
TotalClosedTix: 3
*************************** 3. row ***************************
User: royced
TotalClosedTix: 1
3 rows in set (0.00 sec)

Can someone please help?

Thanks
Winn Johnston

#5

Here is the SQL and stack trace of two queries obtained by using
"Configuration » Tools » SQL Queries".

[snip]

ObjectCustomFieldValues_1.Content < ‘today’

Er, right. I forgot that date and datetime CFs don’t get the same
"natural language" parsing that core datetime fields do. This is a
known bug, and I believe we have a ticket open about it (but maybe not,
can’t find it quickly).

Sorry about that.

#6

Winn,

Are you using the RT SQL provided with the system?

Once you’ve created a search with RTSQL, you save the search and then it is
available to be used in a Dashboard.

Hope this helps.

KennOn Thu, Sep 6, 2012 at 7:54 AM, Winn Johnston winn_johnston@yahoo.comwrote:

All,

I am trying to create a top closers list on the default dashboard. I have
the exact SQL statment i want to use, but i am lost after that.

select u.Name as User, count(1) as TotalClosedTix from Users u join
Tickets t on u.id = t.owner where t.Status = “resolved” group by u.Name
order by 2 desc limit 10 \G;

*************************** 1. row ***************************
User: winnj
TotalClosedTix: 6
*************************** 2. row ***************************
User: dennisc
TotalClosedTix: 3
*************************** 3. row ***************************
User: royced
TotalClosedTix: 1
3 rows in set (0.00 sec)

Can someone please help?

Thanks
Winn Johnston

#7

I am trying to create a top closers list on the default dashboard. I
have the exact SQL statment i want to use, but i am lost after that.

RT doesn’t let you run raw SQL. You get TicketSQL, which is a SQL-like
query language for searching tickets in RT. (This is what Kenn called
RTSQL in his reply to you.)

Your easiest solution is to create a search on “Status = ‘resolved’” in
RT’s query builder, then click on the Chart, and choose to group by
Owner Name. Now save the chart with a name, and go add that named chart
to your RT at a Glance page or custom dashboard.

#8

Hi,

Take a look at SummaryByUser extension. It shows only active statuses,
but it’s possible to change statuses.On Thu, Sep 6, 2012 at 6:54 PM, Winn Johnston winn_johnston@yahoo.com wrote:

All,

I am trying to create a top closers list on the default dashboard. I have
the exact SQL statment i want to use, but i am lost after that.

select u.Name as User, count(1) as TotalClosedTix from Users u join Tickets
t on u.id = t.owner where t.Status = “resolved” group by u.Name order by 2
desc limit 10 \G;

*************************** 1. row ***************************
User: winnj
TotalClosedTix: 6
*************************** 2. row ***************************
User: dennisc
TotalClosedTix: 3
*************************** 3. row ***************************
User: royced
TotalClosedTix: 1
3 rows in set (0.00 sec)

Can someone please help?

Thanks
Winn Johnston

Best regards, Ruslan.