Just a bug report and a hopefully simple fix. The chart option
for Created Daily, Created Monthly,… gives the following
error when run:
Feb 3 10:54:23 rt1 RT: RT::Handle=HASH(0xa891050) couldn’t execute the query 'SELECT COUNT(main.id) AS id, SUBSTR(Created,1,7) AS createdmonthly FROM Tickets main WHERE (main.Status != ‘deleted’) AND (main.Created > ‘2008-01-01 06:00:00’ AND main.Created < ‘2009-01-01 06:00:00’) AND (main.Type = ‘ticket’) AND (main.EffectiveId = main.id) GROUP BY SUBSTR(Created,1,7) ’ at /usr/site/perl-5.8.8/lib/site_perl/5.8.8/DBIx/SearchBuilder/Handle.pm line 518 DBIx::SearchBuilder::Handle::SimpleQuery(‘RT::Handle=HASH(0xa891050)’, ‘SELECT COUNT(main.id) AS id, SUBSTR(Created,1,7) AS createdmo…’) called at /usr/site/perl-5.8.8/lib/site_perl/5.8.8/DBIx/SearchBuilder.pm line 238 DBIx::SearchBuilder::_DoSearch(‘RT::Report::Tickets=HASH(0x86a2630)’) called at /usr/site/rt-3.8/DEV/bin/…/lib/RT/Tickets_Overlay.pm line 2672 RT:
:_DoSearch(‘RT::Report::Tickets=HASH(0x86a2630)’) called at /usr/site/rt-3.8/DEV/bin/…/local/lib/RT/Report/Tickets.pm line 152 RT::Report::_DoSearch('RT::Report::Tickets=HASH(
Feb 3 10:54:25 rt1 RT: DBD::Pg::st execute failed: ERROR: function substr(timestamp without time zone, integer, integer) does not exist LINE 1: SELECT COUNT(main.id) AS id, SUBSTR(Created,1,7) AS createdm… ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. at /usr/site/perl-5.8.8/lib/site_perl/5.8.8/DBIx/SearchBuilder/Handle.pm line 505. (/usr/site/perl-5.8.8/lib/site_perl/5.8.8/DBIx/SearchBuilder/Handle.pm:505)
I fixed the current problem by making a local change to
lib/RT/Report/Tickets.pm to cast the timestamp to text. Hopefully,
there is a fix that will work across all backends:
diff -u lib/RT/Report/Tickets.pm .
— local/lib/RT/Report/Tickets.pm 2009-01-21 13:12:22.000000000 -0600
+++ ./Tickets.pm 2009-02-03 11:06:35.000000000 -0600
@@ -169,13 +169,16 @@
if ($field =~ /^(.*)(Daily|Monthly|Annually)$/) {
my ($field, $grouping) = ($1, $2);
if ( $grouping =~ /Daily/ ) {
-
$args{'FUNCTION'} = "SUBSTR($field,1,10)";
+# $args{‘FUNCTION’} = “SUBSTR($field,1,10)”;
-
$args{'FUNCTION'} = "SUBSTR(${field}::text,1,10)"; } elsif ( $grouping =~ /Monthly/ ) {
-
$args{'FUNCTION'} = "SUBSTR($field,1,7)";
+# $args{‘FUNCTION’} = “SUBSTR($field,1,7)”;
-
$args{'FUNCTION'} = "SUBSTR(${field}::text,1,7)"; } elsif ( $grouping =~ /Annually/ ) {
-
$args{'FUNCTION'} = "SUBSTR($field,1,4)";
+# $args{‘FUNCTION’} = “SUBSTR($field,1,4)”;
-
} elsif ( $field =~ /^(?:CF|CustomField).{(.*)}$/ ) { #XXX: use CFDecipher method$args{'FUNCTION'} = "SUBSTR(${field}::text,1,4)"; }
my $cf_name = $1;
Cheers,
Ken