POSGIS timefilter date_trunc bug

[Denis Nadeau]

Hi,

I have created a database on PostgreSQL version 8.0.3.  I have translated my
shapefile to an sql file using shp2pgsql version 1.1.2 from PostGIS.  I have
created and populated my table correctly with the result sql command file.

gid | location | time | imgdate | the_geom
-----+----------+------+---------+----------

I am using WMS with timedefault, timeitem and timeextent:

    METADATA
        wms_title        "denis"
        wms_timeextent  "2006-01-01/2006-06-18"
          wms_timeitem    "time"
        wms_timedefault "2006-06-10"
        wms_extent      "-180.0 -90.0 180.0 102.96"

    END

MapSever connects to  postgresql correctly.
My filter is translated to:

"date_trunc('day', time) = '2006-06-10'

When I run mapserver I get the following message:

content-type: application/vnd.ogc.se_xml

<?xml version='1.0' encoding="ISO-8859-1" standalone="no" ?>
<!DOCTYPE ServiceExceptionReport SYSTEM "
http://schemas.opengeospatial.net/wms/1.1.1/exception_1_1_1.dtd">
<ServiceExceptionReport version="1.1.1">
<ServiceException>
msDrawMap(): Image handling error. Failed to draw layer named
&#39;AIRSRGB2&#39;.
prepare_database(): Query error. Error executing POSTGIS DECLARE (the actual
query) statement: &#39;DECLARE mycursor BINARY CURSOR FOR SELECT
&quot;time&quot;::text,&quot;location&quot;::text,asbinary(force_collection(force_2d(the_geom)),&#39;NDR&#39;),gid::text
from mapserver WHERE (date_trunc(&#39;day&#39;, time) =
&#39;2006-06-10&#39;) and (the_geom &amp;&amp; setSRID( &#39;BOX3D(-179.82 -
89.91,179.82 89.91)&#39;::BOX3D,find_srid(&#39;&#39;,&#39;mapserver&#39;,&#39;the_geom&#39;)
))&#39;

Postgresql reports the error as &#39;ERROR:  function
date_trunc(&quot;unknown&quot;, character varying) does not exist
HINT:  No function matches the given name and argument types. You may need
to add explicit type casts.
&#39;

More Help:

Error with POSTGIS data variable. You specified &#39;check your .map
file&#39;.
Standard ways of specifiying are :
(1) &#39;geometry_column from geometry_table&#39;
(2) &#39;geometry_column from (sub query) as foo using unique column name
using SRID=srid#&#39;

Make sure you put in the &#39;using unique  column name&#39; and &#39;using
SRID=#&#39; clauses in.

For more help, please see http://postgis.refractions.net/documentation/

Mappostgis.c - version of Jan 23/2004.

</ServiceException>
</ServiceExceptionReport>

Regards,
Denis
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.osgeo.org/pipermail/mapserver-users/attachments/20060628/e9aaab98/attachment.html