<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head>
<meta content="text/html; charset=UTF-8" http-equiv="Content-Type"/>
</head><body style="">
<div>
<div>
Hi,
</div>
<div>
we had the same issue.
</div>
<div>
When we call mapserver via cgi, we set the apache environment for oracle like this:
</div>
<div>
SetEnv NLS_LANG=German_Germany.UTF8
<br/>SetEnv NLS_DATE_FORMAT="YYYY-MM-DD HH24:MI:SS"
</div>
<div>
</div>
<div>
So, we can use a MapServer supported time format.
</div>
<div>
</div>
<div>
When we call mapserver via fastcgi, we set the environment for oracle in a script:
</div>
<div>
</div>
<div>
<br/>#!/bin/sh
<br/>export ORACLE_HOME="(...)/instantclient_11_2
<br/>export ORACLE_BASE="(...)/instantclient_11_2
<br/>export LD_LIBRARY_PATH="(...)/instantclient_11_2
<br/>export TNS_ADMIN="(...)/instantclient_11_2
<br/>export NLS_LANG=German_Germany.UTF8
<br/>export NLS_DATE_FORMAT="YYYY-MM-DD HH24:MI:SS"
<br/>MAPSERV="(...)/mapserv.fcgi"
<br/>MS_MAPFILE="(...)/wms_geoportal.map" exec ${MAPSERV}
<br/>
<br/>
</div>
<div>
regards,
</div> Stefan
</div>
<blockquote style="position: relative; margin-left: 0px; padding-left: 10px; border-left: solid 1px blue;" type="cite">
"D. Nappo" <domenico.nappo@gmail.com> hat am 21. Januar 2014 um 15:18 geschrieben:
<br/>
<br/>
<div dir="ltr">
...but unfortunately MapServer wms time support doesn't include that format:
<div>
</div>
<div>
<a href="http://mapserver.org/it/ogc/wms_time.html">http://mapserver.org/it/ogc/wms_time.html</a>
</div>
</div>
<div class="gmail_extra">
<br/>
<br/>
<div class="gmail_quote">
2014/1/21 D. Nappo
<span><<a target="_blank" href="mailto:domenico.nappo@gmail.com">domenico.nappo@gmail.com</a>></span>
<br/>
<blockquote style="margin: 0 0 0 .8ex; border-left: 1px #ccc solid; padding-left: 1ex;">
<div dir="ltr">
Many thanks!
<div>
It helped: the default date format in our system is DD-Mon-RR and I found it out with SELECT * FROM nls_database_parameters WHERE parameter LIKE '%DATE%'
</div>
<div>
</div>
</div>
<div class="HOEnZb">
<div class="h5">
<div class="gmail_extra">
<br/>
<br/>
<div class="gmail_quote">
2014/1/20 Umberto Nicoletti
<span><<a target="_blank" href="mailto:umberto.nicoletti@gmail.com">umberto.nicoletti@gmail.com</a>></span>
<br/>
<blockquote style="margin: 0 0 0 .8ex; border-left: 1px #ccc solid; padding-left: 1ex;">
<div dir="ltr">
<div class="gmail_extra">
<div class="gmail_quote">
<div>
On Mon, Jan 20, 2014 at 2:44 PM, D. Nappo
<span><<a target="_blank" href="mailto:domenico.nappo@gmail.com">domenico.nappo@gmail.com</a>></span> wrote:
<br/>
<blockquote style="margin: 0px 0px 0px 0.8ex; border-left-width: 1px; border-left-color: #cccccc; border-left-style: solid; padding-left: 1ex;">
<div dir="ltr">
Hi there,
<div>
</div>
<div>
I couldn't figure out how to solve this. I have a layer so configured:
</div>
<div>
</div>
<div>
<div>
LAYER
</div>
<div>
NAME "layer"
</div>
<div>
TYPE point
</div>
<div>
CONNECTION "conn_string...."
</div>
<div>
CONNECTIONTYPE ORACLESPATIAL
</div>
<div>
TEMPLATE "templates/hotspot_template.html"
</div>
<div>
DATA "SHAPE FROM (SELECT POINT as SHAPE, ACQ_DATE, ACQ_TIME, SATELLITE, CONFIDENCE, VERSION, BRIGHT_T31, FRP FROM gwsprd.HOT_SPOTS_MODIS) using unique OGR_FID"
</div>
<div>
METADATA
</div>
<div>
"wms_title" "WMS test"
</div>
<div>
"wms_srs" "EPSG:4326"
</div>
<div>
"wms_extent" "-180 -90 180 90"
</div>
<div>
"wms_timeextent" "2000-01-01/2020-12-31"
</div>
<div>
"wms_timeitem" "acq_date"
</div>
<div>
"wms_timedefault" "2014-01-01"
</div>
<div>
"wms_timeformat" "YYYY-MM-DD"
</div>
</div>
</div>
</blockquote>
<div>
</div>
<div>
</div>
</div>
<div>
Try with this:
</div>
<div>
</div>
<div>
"wms_timeformat" "DD-MM-YY"
</div>
<div>
</div>
<div>
it is the default format Oracle expects when converting a string to a date and no format has been explicitly provided.
</div>
<div>
</div>
<div>
Hth,
</div>
<div>
Umberto
</div>
<div>
</div>
<div>
</div>
<blockquote style="margin: 0px 0px 0px 0.8ex; border-left-width: 1px; border-left-color: #cccccc; border-left-style: solid; padding-left: 1ex;">
<div>
<div dir="ltr">
<div>
<div>
"wms_enable_request" "*"
</div>
<div>
END
</div>
<div>
<span> </span>CLASS
</div>
<div>
SYMBOL 'circle'
</div>
<div>
SIZE 2
</div>
<div>
COLOR 255 0 0
</div>
<div>
END
</div>
<div>
END
</div>
</div>
<div>
</div>
<div>
</div>
<div>
</div>
<div>
Now, the date column in the oracle table is the acq_date field.
</div>
<div>
</div>
<div>
</div>
<div>
The problem is that Mapserver translates that with a wrong oracle query (which it works in postgreslq, I guess):
</div>
<div>
</div>
<div>
<div>
SELECT OGR_FID,rownum, SHAPE FROM
</div>
<div>
(SELECT OGR_FID, POINT as SHAPE, ACQ_DATE, ACQ_TIME, SATELLITE, CONFIDENCE, VERSION, BRIGHT_T31, FRP FROM gwsprd.HOT_SPOTS_MODIS)
</div>
<div>
WHERE acq_date = '2014-01-16'
</div>
</div>
<div>
</div>
<div>
</div>
<div>
The query above uses a wrong filter and it gives the error:
</div>
<div>
</div>
<div>
ORA-01861: literal does not match format string
</div>
<div>
</div>
<div>
</div>
<div>
How can I tell to Mapserver to use a correct query??? Or do I missing anyhting???
</div>
<div>
Something like:
</div>
<div>
</div>
<div>
WHERE acq_date = to_date('2014-01-16','YYYY-MM-DD')
</div>
</div>
</div> _______________________________________________
<br/> mapserver-users mailing list
<br/>
<a target="_blank" href="mailto:mapserver-users@lists.osgeo.org">mapserver-users@lists.osgeo.org</a>
<br/>
<a target="_blank" href="http://lists.osgeo.org/mailman/listinfo/mapserver-users">http://lists.osgeo.org/mailman/listinfo/mapserver-users</a>
</blockquote>
</div>
</div>
</div>
</blockquote>
</div>
</div>
</div>
</div>
</blockquote>
</div>
</div>
</blockquote>
<div>
<br/>
</div>
</body></html>