[rt-users] Difference in time zone handling between database, 3.6 and 3.8

[Tom Lanyon]

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