From owner-freebsd-www@FreeBSD.ORG Thu Apr 5 08:20:08 2012 Return-Path: Delivered-To: freebsd-www@hub.freebsd.org Received: from mx1.freebsd.org (mx1.freebsd.org [IPv6:2001:4f8:fff6::34]) by hub.freebsd.org (Postfix) with ESMTP id CC84B106566C for ; Thu, 5 Apr 2012 08:20:08 +0000 (UTC) (envelope-from gnats@FreeBSD.org) Received: from freefall.freebsd.org (freefall.freebsd.org [IPv6:2001:4f8:fff6::28]) by mx1.freebsd.org (Postfix) with ESMTP id 9DAFA8FC16 for ; Thu, 5 Apr 2012 08:20:08 +0000 (UTC) Received: from freefall.freebsd.org (localhost [127.0.0.1]) by freefall.freebsd.org (8.14.5/8.14.5) with ESMTP id q358K8Jf096528 for ; Thu, 5 Apr 2012 08:20:08 GMT (envelope-from gnats@freefall.freebsd.org) Received: (from gnats@localhost) by freefall.freebsd.org (8.14.5/8.14.5/Submit) id q358K85U096527; Thu, 5 Apr 2012 08:20:08 GMT (envelope-from gnats) Resent-Date: Thu, 5 Apr 2012 08:20:08 GMT Resent-Message-Id: <201204050820.q358K85U096527@freefall.freebsd.org> Resent-From: FreeBSD-gnats-submit@FreeBSD.org (GNATS Filer) Resent-To: freebsd-www@FreeBSD.org Resent-Reply-To: FreeBSD-gnats-submit@FreeBSD.org, Veselin Slavov Received: from mx1.freebsd.org (mx1.freebsd.org [IPv6:2001:4f8:fff6::34]) by hub.freebsd.org (Postfix) with ESMTP id 0AB77106566B for ; Thu, 5 Apr 2012 08:14:17 +0000 (UTC) (envelope-from nobody@FreeBSD.org) Received: from red.freebsd.org (red.freebsd.org [IPv6:2001:4f8:fff6::22]) by mx1.freebsd.org (Postfix) with ESMTP id EA20B8FC12 for ; Thu, 5 Apr 2012 08:14:16 +0000 (UTC) Received: from red.freebsd.org (localhost [127.0.0.1]) by red.freebsd.org (8.14.4/8.14.4) with ESMTP id q358EGjU045439 for ; Thu, 5 Apr 2012 08:14:16 GMT (envelope-from nobody@red.freebsd.org) Received: (from nobody@localhost) by red.freebsd.org (8.14.4/8.14.4/Submit) id q358EG9E045423; Thu, 5 Apr 2012 08:14:16 GMT (envelope-from nobody) Message-Id: <201204050814.q358EG9E045423@red.freebsd.org> Date: Thu, 5 Apr 2012 08:14:16 GMT From: Veselin Slavov To: freebsd-gnats-submit@FreeBSD.org X-Send-Pr-Version: www-3.1 Cc: Subject: www/166662: www/asterisk-stat - pgsql select substring() problem X-BeenThere: freebsd-www@freebsd.org X-Mailman-Version: 2.1.5 Precedence: list List-Id: FreeBSD Project Webmasters List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Thu, 05 Apr 2012 08:20:09 -0000 >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: