Hi Gabriel, I turned on the GEOTOOLS_DEVELOPER_LOGGING it told me that PropertyIsLessThanEqualTo is not defined in the gt-xsd-filter-2.6.1.jar!/org/geotools/filter/v1_1/filter.xsd.
Looking at this xsd it says PropertyIsLessThanOrEqualTo so I was missing the 'Or' bit. The PropertyIsGreaterThanOrEqualTo and PropertyIsLessThanOrEqualTo operator then seem to work but only 'partially'. I was misled by the ogc:ComparisonOperators given in the WFS 1.1.0 getCapabilties statement which are: <ogc:ComparisonOperator>LessThanEqualTo</ogc:ComparisonOperator> <ogc:ComparisonOperator>GreaterThanEqualTo</ogc:ComparisonOperator> This seems inconsistent, shouldn't the same operator name be in both the filter.xsd and the getCapabilities statement, not sure? I say 'partially' because of this test that I ran Bilgola >=2010-12-12 08:58:00 and =< 2011-01-06 09:12:00 Expect 2 records but only got first one back at 2010-12-12 08:58:00. Had to add 1 second to second time i.e 2011-01-06 09:12:01 to get it back http://server.metoc.gov.au:8080/geoserver/wfs?&VERSION=1.1.0&SERVICE=WFS&REQUEST=GetFeature&; TYPENAME=TEST:TEST.BEACH_TEMPS&Filter=<Filter><And><PropertyIsEqualTo><PropertyName>LOCATION</PropertyName> <Literal>Bilgola</Literal></PropertyIsEqualTo><PropertyIsGreaterThanOrEqualTo><PropertyName>OBS_DATE_TIME</PropertyName><Function name="dateParse"><Literal>yyyy-MM-dd HH:mm:ss</Literal><Literal>2010-12-12 08:58:00</Literal></Function></PropertyIsGreaterThanOrEqualTo><PropertyIsLessThanOrEqualTo><PropertyName>OBS_DATE_TIME</PropertyName><Function name="dateParse"><Literal>yyyy-MM-dd HH:mm:ss</Literal><Literal>2011-01-06 09:12:00</Literal></Function></PropertyIsLessThanOrEqualTo></And></Filter> The above query worked fine straight through Oracle SQL getting 2 records back. However through Geoserver WFS only 1 record comes back i.e the <=2011-01-06 09:12:00 part of the logic doesn't seem to be working. The arcsde.data.ArcSDEQuery appears to passing in the correct date/times as shown in the excerpt form the geoserver.log: 2011-04-13 13:57:44,931 DEBUG [org.geotools.arcsde.data.ArcSDEQuery] - Unsupported (and therefore ignored) portion of SDE Query: '[[ OBS_DATE_TIME >= dateParse([yyyy-MM-dd HH:mm:ss], [2010-12-12 08:58:00]) ] AND [ OBS_DATE_TIME <= dateParse([yyyy-MM-dd HH:mm:ss], [2011-01-06 09:12:00]) ]]' 2011-04-13 13:57:44,931 DEBUG [org.geotools.arcsde.data] - Supported filters: [ LOCATION = Bilgola ] --- Filter.INCLUDE 2011-04-13 13:57:44,931 DEBUG [org.geotools.arcsde.data] - Unsupported filter: [[ OBS_DATE_TIME >= dateParse([yyyy-MM-dd HH:mm:ss], [2010-12-12 08:58:00]) ] AND [ OBS_DATE_TIME <= dateParse([yyyy-MM-dd HH:mm:ss], [2011-01-06 09:12:00]) ]] I asked our Oracle DBA if there is way of seeing the SQL that gets to Oracle and he says its not logged. Is there some way of debugging Geotools so you can see the actual SQL statement that gets sent to Oracle? As you say it could be something to do with the adding of the random 1/1000 seconds to the date_time? We need to know how the time comparisons are being done. If it was doing something like IF ('2011-01-06 09:12:00' <= ''2011-01-06 09:12:00.888") then the '=' part of the <= may never work because of the addition of the 1/1000 second bit. Thanks, Andrew ----- Original Message ----- From: "Gabriel Roldán" <grol...@opengeo.org> To: "andrew walsh" <awa...@metoc.gov.au> Cc: <geoserver-users@lists.sourceforge.net>; "Kathleen Hirst" <k.hi...@bom.gov.au> Sent: Wednesday, April 13, 2011 10:47 AM Subject: Re: Date comparisons with OGC filter and CQL filter > On Wed, 2011-04-13 at 10:10 +1000, andrew walsh wrote: >> Hi Gabriel, >> >> Sending the below message again for benefit of the list is it looks like it >> bounced off the >> geoserver-users list as it was from my gmail and said 'awaiting moderator >> approval':- >> >> Thanks for your reply. >> I am not so familiar the geoserver Java code workings and not in a position >> to debug/compile and submit a patch. However I would be happy to raise this >> issue on JIRA for the GeoTools ArcSDE plugin. Can't remember my login details >> for Jira but I will find out when I get back to the office and raise >> a ticket. > ok, sounds good. >> >> There were 2 other issues which I raised in my initial email on April 11 for >> which I >> have not got any opinions yet, so comments please from >> anyone out there in the Geoserver community: >> >> 1) the addition of a random 1/1000 sec value being added to the DATE >> when it comes back in GML or CSV from getFeature. To me this doesn't seem >> correct. That is if you have DATE like 2000-01-01 09:00:01 in >> Oracle then it should return a ISO8601 style TIMESTAMP >> like '2000-01-01T09:00:01.000' not e.g "'2000-01-01T09:00:01.067"". >> What's the view on this behaviour, is this a bug worth >> reporting as well? >> >> 2) With date comparison the operators PropertyIsGreaterThan (>) and >> PropertyIsLessThan (<) work OK but PropertyIsLessThanEqualTo (<=) and >> PropertyIsGreaterThanEqualTo (>=) do not work, that is the >> <= and >= filter had no effect and returned 'True'. It would be >> much better if this worked correctly as we frequently need an inclusive >> range of date/time. > may this be related to 1), like in it doesn't work because of that > little difference? > Can you check (somehow, I know almost nothing about oracle) the Oracle > logs and see what request is coming in, and how it should actually be? > > Cheers, > Gabriel >> >> Thanks and Regards, >> >> Andrew >> > > -- > Gabriel Roldan > grol...@opengeo.org > Expert service straight from the developers > > > ------------------------------------------------------------------------------ Forrester Wave Report - Recovery time is now measured in hours and minutes not days. Key insights are discussed in the 2010 Forrester Wave Report as part of an in-depth evaluation of disaster recovery service providers. Forrester found the best-in-class provider in terms of services and vision. Read this report now! http://p.sf.net/sfu/ibm-webcastpromo _______________________________________________ Geoserver-users mailing list Geoserver-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/geoserver-users
