Hi all,
I’m running some searches to find tickets from a certain queue which
were created in February but having some strange issues with time zones.
Assuming that the ‘created’ column in the database is UTC (which it
seems to be), querying the database directly tells me there was 816
tickets:
rt=# select count(*) from tickets t where
rt-# extract(‘month’ from t.created at time zone ‘UTC’) = 2
rt-# and extract(‘year’ from t.created at time zone ‘UTC’) = 2008
rt-# and t.status not in (‘rejected’,‘deleted’) and t.queue = 9;
count
816
RT Web and command line interfaces give different results to the
database.
RT CLI pointing to a RT 3.6.1 instance says there’s 788:
# rt ls -i “Queue = ‘Testing’ and Status != ‘rejected’ and Created >
‘2008-01-31’ and Created < ‘2008-03-01’” | wc -l
788
Same RT CLI pointing to an RT 3.8.0rc2 staging instance (with a
complete copy of the 3.6.1 instances data) says there’s 786:
# rt ls -i “Queue = 'Testing and Status != ‘rejected’ and Created >
‘2008-01-31’ and Created < ‘2008-03-01’” | wc -l
786
Looking at the actual tickets that differ, we can see that they’re all
outside the window I’m filtering on:
# select id,created at time zone ‘UTC’ from tickets where id in
(54067,54071,54072,59363) order by id;
id | timezone
54067 | 2008-01-31 09:11:19+10:30
54071 | 2008-01-31 09:56:56+10:30
54072 | 2008-01-31 10:05:57+10:30
59363 | 2008-03-01 09:50:40+10:30
The RT 3.6 and 3.8 instances are on different servers, but both are
running the same tzdata (2007k) and both sync from the same NTP server
(the same that the database syncs from). Both RT instances have the
$Timezone variable set the same.
Does anyone know why this could be and, more importantly, which is
accurate? 
Also, please let me know if this should go to -devel or -bugs instead
of -users.
Regards,
Tom