Hello,
I have stuck into a problem handling postgres date columns through GeoServer.
My GeoServer subtracts a day from the actual date (ie 1 June --> 31 May) when the raw date falls in the period of summer time. Details are described below.
As suggested in the other threads, I have configured GeoServer to run in GMT by adding JAVA_OPTS="-Duser.timezone=GMT" to tomcat7.conf but it still keeps tampering the date values.
I guess this is caused by the same root of GEOS-6319 (https://jira.codehaus.org/browse/GEOS-6319). I personally do not get why GeoServer has to apply timezones to date-only fields. They should be treated differently from date-and-hour fields.
I hope this issue would be fixed in the next release.
Environment
OS: Amazon Linux (Locale=en_US.UTF8, Timezone=GMT)
GeoServer 2.6.0 (on Tomcat 7)
Postgres 9.3.3 (on Amazon RDS)
PostGIS: "POSTGIS="2.1.0 r11822" GEOS="3.4.2-CAPI-1.8.2 r3921" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.10.1, released 2013/08/26" LIBXML="2.9.1" LIBJSON="UNKNOWN" RASTER"
Reproduction Steps
1. Create a database with PostGIS extension and run the following SQL lines.
ALTER DATABASE sample SET timezone=GMT;
DROP TABLE date_test;
CREATE TABLE date_test (
geom geometry(Geometry,3857),
id integer NOT NULL,
eventdate date,
CONSTRAINT pkey_date_test PRIMARY KEY (id)
) WITH ( OIDS=FALSE );
INSERT INTO date_test VALUES(ST_GeomFromText('POINT(-13329 6710470)', 3857), 1, '2014-03-30'); -- Rec 1
INSERT INTO date_test VALUES(ST_GeomFromText('POINT(-13329 6710470)', 3857), 2, '2014-03-31'); -- Rec 2
INSERT INTO date_test VALUES(ST_GeomFromText('POINT(-13329 6710470)', 3857), 3, '2014-10-26'); -- Rec 3
INSERT INTO date_test VALUES(ST_GeomFromText('POINT(-13329 6710470)', 3857), 4, '2014-10-27'); -- Rec 4
-- In 2014, summer time was from 30 March to 26 October in UK and Europe
2. Publish date_test to GeoServer from either PostGIS or PostGIS(JNDI) connection.
3. Make the following WFS getfeatures request
http://localhost:8080/geoserver /ows?service=wfs&request=GetFeature&version=1.1.0&cql_filter=%22geom%22%20IS%20NOT%20NULL&typeNames= date_test
4. In my case, the eventdate properties in the WFS result were
Rec1: '2014-03-30Z' --> OK
Rec2: '2014-03-30Z' --> minus 1 day
Rec3: '2014-10-25Z' --> minus 1 day
Rec4: '2014-10-27Z' --> OK
Go Sato
Graduate GIS Developer
WSP UK
Website: www.wspgroup.co.uk