[rt-users] Chart problem with PostgreSQL 8.3

[Kenneth Marshall]

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::Tickets::_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::Tickets::_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)";
+            $args{'FUNCTION'} = "SUBSTR(${field}::text,1,4)";
         }
     } elsif ( $field =~ /^(?:CF|CustomField)\.{(.*)}$/ ) { #XXX: use CFDecipher method
         my $cf_name = $1;
--------------------------------

Cheers,
Ken