Hi all,
we are having problems retrieving data using WFS from a oracle datastore, specifically related to dates and timestamps. We have read other posts in which other people had similar problems, but not found response.
Our WFS filters are similar to:
<Ogc:Filter xmlns:ogc = “http://www.opengis.net/ogc”>
Ogc:PropertyIsBetween
Ogc:PropertyNameFH_TIMESTAMP</ogc:PropertyName>
Ogc:LowerBoundary
Ogc:Literal2015-06-08 12:00:00</ogc:Literal>
</Ogc:LowerBoundary>
Ogc:UpperBoundary
Ogc:Literal2015-06-08 13:00:00</ogc:Literal>
</Ogc:UpperBoundary>
</Ogc:PropertyIsBetween>
…
Here in Spain, we work in GMT+1. The FH_TIMESTAMP field is defined as TIMESTAMP in the related table. Tomcat where GeoServer is deployed is set as defaulta to GMT.
This query must return a single result with date/time 2015-06-08 12:12:01. Indeed it is recovering, but the response shows 10:12:01 instead of 12:12:01. Also, the date field (Date type in BD … ), from which the timestamp is generated, is one day late:
Xxx:FH_FECHA2015-06-07Z</xxx:FH_FECHA>
xxx:FH_TIMESTAMP2015-06-08T10:12:01Z</xxx:FH_TIMESTAMP>
Geoserver The log shows the following information for this request:
03/07/2016 13: 58: 27.468 DEBUG [geotools.jdbc] - SELECT * FROM (? SELECT FH_FECHA, FH_TIMESTAMP, …, as GEOM GEOM FROM … WHERE (FH_TIMESTAMP BETWEEN ? AND ?)) WHERE ROWNUM <= 2000
03/07/2016 13: 58: 27.468 DEBUG [geotools.jdbc] - 1 = 2015-06-08 12:00:00.0
07/03/2016 13: 58: 27.468 DEBUG [geotools.jdbc] - 2 = 2015-06-08 13:00:00.0
07/03/2016 13: 58: 27.503 DEBUG [geotools.jdbc] - 2015-06-08 12: 12:01.0 is not of type java.sql.Timestamp, attempting conversion
We have tried to make the same request with “geoserver” format. In this case, we have to decrease the range of timestamp in two hours in order to recover the same data:
Ogc:PropertyIsBetween
Ogc:PropertyNameFH_TIMESTAMP</ogc:PropertyName>
Ogc:LowerBoundary
Ogc:Literal2015-06-08T10:00:00Z</ogc:Literal>
</Ogc:LowerBoundary>
Ogc:UpperBoundary
Ogc:Literal2015-06-08T11:00:00Z</ogc:Literal>
</Ogc:UpperBoundary>
</Ogc:PropertyIsBetween>
Result is the same: hour late and erroneous date:
Xxx:FH_FECHA2015-06-07Z</xxx:FH_FECHA>
xxx:FH_TIMESTAMP2015-06-08T10:12:01Z</xxx:FH_TIMESTAMP>
Same as for the previous query.
What should we do to show to avoid previous day’s date and time with two hours late?
Any help is welcome.
Thank you very much, greetings