www/166662: www/asterisk-stat - pgsql select substring() problem

[Veselin Slavov-2]

>Number:         166662
>Category:       www
>Synopsis:       www/asterisk-stat - pgsql select substring() problem
>Confidential:   no
>Severity:       non-critical
>Priority:       low
>Responsible:    freebsd-www
>State:          open
>Quarter:        
>Keywords:      
>Date-Required:
>Class:          sw-bug
>Submitter-Id:   current-users
>Arrival-Date:   Thu Apr 05 08:20:08 UTC 2012
>Closed-Date:
>Last-Modified:
>Originator:     Veselin Slavov
>Release:        7.4-STABLE
>Organization:
>Environment:

FreeBSD server.slavof.net 7.4-STABLE FreeBSD 7.4-STABLE #57: Mon Mar 12 14:33:42 EET 2012     [hidden email]:/usr/obj/usr/src/sys/server  i386
>Description:
PG_VERSION = 9.0

While using statustics page for 'cdr report', 'RED TOTAL BAR TABLE' at the bottom not displayed.

Here is part of  /var/log/message:

Apr  5 10:22:37 server postgres[34881]: [2-1] ERROR:  function pg_catalog.substring(timestamp with time zone, integer, integer) does not exist at character 8
Apr  5 10:22:37 server postgres[34881]: [2-2] HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
Apr  5 10:22:37 server postgres[34881]: [2-3] STATEMENT:  SELECT substring(calldate,1,10) AS day, sum(duration) AS calltime, count(*) as nbcall FROM cdr WHERE (calldate) >= ('2012-01-01') AND (calldate) <= ('2012-03-31 23:59:59') GROUP BY substring(calldate,1,10)
>How-To-Repeat:
http://127.0.0.1/asterisk-stat/cdr.php?s=1

Choose any period and click search.

>Fix:
patch file attached

Patch attached with submission follows:

--- call-log.php.orig 2012-04-05 10:48:18.000000000 +0300
+++ call-log.php 2012-04-05 10:48:29.000000000 +0300
@@ -229,7 +229,7 @@
 
 
  /************************/
- $QUERY = "SELECT substring(calldate,1,10) AS day, sum(duration) AS calltime, count(*) as nbcall FROM cdr WHERE ".$FG_TABLE_CLAUSE." GROUP BY substring(calldate,1,10)"; //extract(DAY from calldate)
+ $QUERY = "SELECT substr(cast(calldate as text),1,10) AS day, sum(duration) AS calltime, count(*) as nbcall FROM cdr WHERE ".$FG_TABLE_CLAUSE." GROUP BY substr(cast(calldate as text),1,10)"; //extract(DAY from calldate)
  //echo "$QUERY";
 
 


>Release-Note:
>Audit-Trail:
>Unformatted:
_______________________________________________
[hidden email] mailing list
http://lists.freebsd.org/mailman/listinfo/freebsd-www
To unsubscribe, send any mail to "[hidden email]"