<!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"/>
<style type="text/css"><!-- /* Font Definitions */ @font-face {font-family: Calibri; }
@font-face {font-family: Tahoma; }
/* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {margin: 0cm; margin-bottom: .0001pt; font-size: 12.0pt; font-family: "Times New Roman","serif";}
a:link, span.MsoHyperlink { color: blue; text-decoration: underline;}
a:visited, span.MsoHyperlinkFollowed { color: purple; text-decoration: underline;}
span.Shkpostityyli17 { font-family: "Calibri","sans-serif"; color: #1F497D;}
.MsoChpDefault { font-family: "Calibri","sans-serif"; }
@page WordSection1 { margin: 70.85pt 2.0cm 70.85pt 2.0cm;}
div.WordSection1 {}
--></style>
</head><body style="">
<div>
Hi,
<br/>we did it and it worked, but oracle couldn't use the index of the date column. You would have to create an index of the "to_char(date column)"-column.
</div>
<div>
Stefan
</div>
<div>
</div>
<blockquote style="position: relative; margin-left: 0px; padding-left: 10px; border-left: solid 1px blue;" type="cite">
<!-- [if gte mso 9]> -->
<!-- <![endif] -->
<!-- [if gte mso 9]> -->
<!-- <![endif] --> "Rahkonen Jukka (Tike)" <jukka.rahkonen@mmmtike.fi> hat am 21. Januar 2014 um 15:25 geschrieben:
<br/>
<br/>
<div class="WordSection1">
<p class="MsoNormal"><span style="font-size: 11.0pt; font-family: 'Calibri','sans-serif'; color: #1f497d;">Hi,</span></p>
<p class="MsoNormal"><span style="font-size: 11.0pt; font-family: 'Calibri','sans-serif'; color: #1f497d;"> </span></p>
<p class="MsoNormal"><span style="font-size: 11.0pt; font-family: 'Calibri','sans-serif'; color: #1f497d;">Perhaps you can select the time into a format that Mapserver likes in your DATA by using to_char?</span></p>
<p class="MsoNormal"><span style="font-size: 11.0pt; font-family: 'Calibri','sans-serif'; color: #1f497d;"><a href="http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions180.htm">http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions180.htm</a></span></p>
<p class="MsoNormal"><span style="font-size: 11.0pt; font-family: 'Calibri','sans-serif'; color: #1f497d;"> </span></p>
<p class="MsoNormal"><span style="font-size: 11.0pt; font-family: 'Calibri','sans-serif'; color: #1f497d;">-Jukka Rahkonen-</span></p>
<p class="MsoNormal"><span style="font-size: 11.0pt; font-family: 'Calibri','sans-serif'; color: #1f497d;"> </span></p>
<p class="MsoNormal"><span style="font-size: 11.0pt; font-family: 'Calibri','sans-serif'; color: #1f497d;"> </span></p>
<div style="border: none; border-left: solid blue 1.5pt; padding: 0cm 0cm 0cm 4.0pt;">
<div>
<div style="border: none; border-top: solid #B5C4DF 1.0pt; padding: 3.0pt 0cm 0cm 0cm;">
<p class="MsoNormal"><span style="font-size: 10.0pt; font-family: 'Tahoma','sans-serif';">D. Nappo wrote</span></p>
</div>
</div>
<p class="MsoNormal"><span> </span></p>
<div>
<p class="MsoNormal">...but unfortunately MapServer wms time support doesn't include that format:</p>
<div>
<p class="MsoNormal"> </p>
</div>
<div>
<p class="MsoNormal"><a href="http://mapserver.org/it/ogc/wms_time.html">http://mapserver.org/it/ogc/wms_time.html</a></p>
</div>
</div>
<div>
<p style="margin-bottom: 12.0pt;" class="MsoNormal"> </p>
<div>
<p class="MsoNormal">2014/1/21 D. Nappo <<a target="_blank" href="mailto:domenico.nappo@gmail.com">domenico.nappo@gmail.com</a>></p>
<div>
<p class="MsoNormal">Many thanks!</p>
<div>
<p class="MsoNormal">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%'</p>
</div>
<div>
<p class="MsoNormal"> </p>
</div>
</div>
<div>
<div>
<div>
<p style="margin-bottom: 12.0pt;" class="MsoNormal"> </p>
<div>
<p class="MsoNormal">2014/1/20 Umberto Nicoletti <<a target="_blank" href="mailto:umberto.nicoletti@gmail.com">umberto.nicoletti@gmail.com</a>></p>
<div>
<div>
<div>
<div>
<p class="MsoNormal">On Mon, Jan 20, 2014 at 2:44 PM, D. Nappo <<a target="_blank" href="mailto:domenico.nappo@gmail.com">domenico.nappo@gmail.com</a>> wrote:</p>
<div>
<p class="MsoNormal">Hi there,</p>
<div>
<p class="MsoNormal"> </p>
</div>
<div>
<p class="MsoNormal">I couldn't figure out how to solve this. I have a layer so configured:</p>
</div>
<div>
<p class="MsoNormal"> </p>
</div>
<div>
<div>
<p class="MsoNormal">LAYER</p>
</div>
<div>
<p class="MsoNormal"> NAME "layer"</p>
</div>
<div>
<p class="MsoNormal"> TYPE point</p>
</div>
<div>
<p class="MsoNormal"> CONNECTION "conn_string...."</p>
</div>
<div>
<p class="MsoNormal"> CONNECTIONTYPE ORACLESPATIAL</p>
</div>
<div>
<p class="MsoNormal"> TEMPLATE "templates/hotspot_template.html"</p>
</div>
<div>
<p class="MsoNormal"> 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"</p>
</div>
<div>
<p class="MsoNormal"> METADATA</p>
</div>
<div>
<p class="MsoNormal"> "wms_title" "WMS test"</p>
</div>
<div>
<p class="MsoNormal"> "wms_srs" "EPSG:4326"</p>
</div>
<div>
<p class="MsoNormal"> "wms_extent" "-180 -90 180 90"</p>
</div>
<div>
<p class="MsoNormal"> "wms_timeextent" "2000-01-01/2020-12-31"</p>
</div>
<div>
<p class="MsoNormal"> "wms_timeitem" "acq_date"</p>
</div>
<div>
<p class="MsoNormal"> "wms_timedefault" "2014-01-01"</p>
</div>
<div>
<p class="MsoNormal"> "wms_timeformat" "YYYY-MM-DD"</p>
</div>
</div>
</div>
<div>
<p class="MsoNormal"> </p>
</div>
<div>
<p class="MsoNormal"> </p>
</div>
</div>
<div>
<p class="MsoNormal">Try with this:</p>
</div>
<div>
<p class="MsoNormal"> </p>
</div>
<div>
<p class="MsoNormal">"wms_timeformat" "DD-MM-YY"</p>
</div>
<div>
<p class="MsoNormal"> </p>
</div>
<div>
<p class="MsoNormal">it is the default format Oracle expects when converting a string to a date and no format has been explicitly provided.</p>
</div>
<div>
<p class="MsoNormal"> </p>
</div>
<div>
<p class="MsoNormal">Hth,</p>
</div>
<div>
<p class="MsoNormal">Umberto</p>
</div>
<div>
<p class="MsoNormal"> </p>
</div>
<div>
<p class="MsoNormal"> </p>
</div>
<blockquote style="border: none; border-left: solid #CCCCCC 1.0pt; padding: 0cm 0cm 0cm 6.0pt; margin-left: 4.8pt; margin-right: 0cm;">
<div>
<div>
<div>
<div>
<p class="MsoNormal"> "wms_enable_request" "*"</p>
</div>
<div>
<p class="MsoNormal"> END</p>
</div>
<div>
<p class="MsoNormal">CLASS</p>
</div>
<div>
<p class="MsoNormal"> SYMBOL 'circle'</p>
</div>
<div>
<p class="MsoNormal"> SIZE 2</p>
</div>
<div>
<p class="MsoNormal"> COLOR 255 0 0</p>
</div>
<div>
<p class="MsoNormal"> END</p>
</div>
<div>
<p class="MsoNormal"> END</p>
</div>
</div>
<div>
<p class="MsoNormal"> </p>
</div>
<div>
<p class="MsoNormal"> </p>
</div>
<div>
<p class="MsoNormal"> </p>
</div>
<div>
<p class="MsoNormal">Now, the date column in the oracle table is the acq_date field.</p>
</div>
<div>
<p class="MsoNormal"> </p>
</div>
<div>
<p class="MsoNormal"> </p>
</div>
<div>
<p class="MsoNormal">The problem is that Mapserver translates that with a wrong oracle query (which it works in postgreslq, I guess):</p>
</div>
<div>
<p class="MsoNormal"> </p>
</div>
<div>
<div>
<p class="MsoNormal">SELECT OGR_FID,rownum, SHAPE FROM </p>
</div>
<div>
<p class="MsoNormal">(SELECT OGR_FID, POINT as SHAPE, ACQ_DATE, ACQ_TIME, SATELLITE, CONFIDENCE, VERSION, BRIGHT_T31, FRP FROM gwsprd.HOT_SPOTS_MODIS) </p>
</div>
<div>
<p class="MsoNormal">WHERE acq_date = '2014-01-16'</p>
</div>
</div>
<div>
<p class="MsoNormal"> </p>
</div>
<div>
<p class="MsoNormal"> </p>
</div>
<div>
<p class="MsoNormal">The query above uses a wrong filter and it gives the error:</p>
</div>
<div>
<p class="MsoNormal"> </p>
</div>
<div>
<p class="MsoNormal">ORA-01861: literal does not match format string</p>
</div>
<div>
<p class="MsoNormal"> </p>
</div>
<div>
<p class="MsoNormal"> </p>
</div>
<div>
<p class="MsoNormal">How can I tell to Mapserver to use a correct query??? Or do I missing anyhting???</p>
</div>
<div>
<p class="MsoNormal">Something like:</p>
</div>
<div>
<p class="MsoNormal"> </p>
</div>
<div>
<p class="MsoNormal">WHERE acq_date = to_date('2014-01-16','YYYY-MM-DD')</p>
</div>
</div>
<p class="MsoNormal"> </p>
</div>
<p class="MsoNormal">_______________________________________________<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></p>
</blockquote>
</div>
<p class="MsoNormal"> </p>
</div>
</div>
</div>
<p class="MsoNormal"> </p>
</div>
</div>
</div>
</div>
<p class="MsoNormal"> </p>
</div>
</div>
</div>
</blockquote>
<div>
<br/>
</div>
</body></html>