[rt-users] RT Ticket Counts and why they differ

[Shawn Scantland]

So just a quick question so that I can put my mind to rest.  Why is the
ticket count for created tickets on any one particular day different when
you compare the data in the database to what's coming out of the API?

For example:
On a particular day in one of our queues the rt CLI command

rt ls -i "Queue = 'support' and Created >= '2007-08-23 00:00:00' and Created
<= '2007-08-23 23:59:59'" | wc -l

will return 12 as the count of tickets.  Now if I were to take into account
that the database stores datetime in localtime but the API wants to use UTC
for the days

rt ls -i "Queue = 'support' and Created >= '2007-08-22 17:00:00' and Created
<= '2007-08-23 16:59:59'" | wc -l (assuming I am doing the math right as the
database is using PDT (GMT -7) )

will return 12 as well (coincidentally).

Now if I query the database with a similar query

select count(*) from Tickets where Created >= '2007-08-23 00:00:00' and
Created <= '2007-08-23 23:59:59' and Queue =1; (where support == 1 for
Queue)

I get 19 records.  If I modify the record for UTC

select count(*) from Tickets where Created >= '2007-08-22 17:00:00' and
Created <= '2007-08-23 16:59:59' and Queue =1;

I get 25 records.

I am assuming some filtering is going on here, but I wonder why that is.  If
I want a count of tickets created on one day (UTC conversions aside)
shouldn't the database record counts and the RT API results match?  Is it
only considering tickets that are active?  Is it removing resolved tickets?
Is there a table join going on that is restricting the results?

This may be very basic but I haven't been able to find it anywhere in the RT
wiki or lists.

Thanks,
Shawn Scantland
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20070827/b2b07d78/attachment.htm>