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
'AIRSRGB2'.
prepare_database(): Query error. Error executing POSTGIS DECLARE (the actual
query) statement: 'DECLARE mycursor BINARY CURSOR FOR SELECT
"time"::text,"location"::text,asbinary(force_collection(force_2d(the_geom)),'NDR'),gid::text
from mapserver WHERE (date_trunc('day', time) =
'2006-06-10') and (the_geom && setSRID( 'BOX3D(-179.82 -
89.91,179.82 89.91)'::BOX3D,find_srid('','mapserver','the_geom')
))'

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

More Help:

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

Make sure you put in the 'using unique  column name' and 'using
SRID=#' 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.htm>