Date: Thu, 5 Apr 2012 08:14:16 GMT From: Veselin Slavov <vess@slavof.net> To: freebsd-gnats-submit@FreeBSD.org Subject: www/166662: www/asterisk-stat - pgsql select substring() problem Message-ID: <201204050814.q358EG9E045423@red.freebsd.org> Resent-Message-ID: <201204050820.q358K85U096527@freefall.freebsd.org>
next in thread | raw e-mail | index | archive | help
>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 vess@server.slavof.net:/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:
Want to link to this message? Use this URL: <https://mail-archive.FreeBSD.org/cgi/mid.cgi?201204050814.q358EG9E045423>