[Geoserver-users] Day light saving time in CQL

I am trying to make a cql request to a local instance of geoserver with a postgres backend
Requests such as
http://localhost:8090/geoserver/wfs?service=wfs&version=2.0.0&request=GetFeature&typeName=windproc:windproc_view&outputFormat=application/json&CQL_FILTER=BBOX(geom%2C-12.875%2C26.125%2C42.125%2C55.125%2C%27EPSG%3A4326%27)%20AND%20reading_time%3D2018-12-25T06%3A00%3A00Z&srsname=EPSG:4326
Work ie the date is 2018-12-25
Making a request such as
http://localhost:8090/geoserver/wfs?service=wfs&version=2.0.0&request=GetFeature&typeName=windproc:windproc_view&outputFormat=application/json&CQL_FILTER=BBOX(geom%2C-12.875%2C26.125%2C42.125%2C55.125%2C%27EPSG%3A4326%27)%20AND%20reading_time%3D2018-06-25T06%3A00%3A00Z&srsname=EPSG:4326
returns nothing!
A bit of digging in the logs and I can I can see that a request e date and time has been translated into a request for data from the database of using 2018-06-25 0700, since the database only has records for 0600 it does’nt find any data.

Basically geoserver is converting a date into daylight saving time format, and then making a request in the database.

Q. How do I tell the geoserver , not to convert a date in cql into a daylight saving version ?

I assume the issue is date format issue, I normally supply a date as something like

2018-12-25T06:00:00.000Z

Thanks in advance for any help.

Dave.

As a follow up to my last email, this is a part of the log, if you trace through the log, you can see geoserver received the correct time parsed it and then turned in to a query string. Its at this point were the date format conversion seems to happen.

ie CQL_FILTER=BBOX(geom%2C-12.875%2C26.125%2C42.125%2C55.125%2C%27EPSG%3A4326%27)%20AND%20reading_time%3D2018-05-27T12%3A00%3A00Z&srsname=EPSG:4326 with /**

becomes
To gt2: Query:
feature type: windproc_view
filter: [[ geom bbox ReferencedEnvelope[-12.875 : 42.125, 26.125 : 55.125] DefaultGeographicCRS[EPSG:WGS 84] AXIS[“Geodetic longitude”, EAST] AXIS[“Geodetic latitude”, NORTH] ] AND [ reading_time = Sun May 27 13:00:00 BST 2018 ]]

database table is
Column | Type | Collation | Nullable | Default
--------------±----------------------------±----------±---------±--------
id | integer | | |
geom | geometry(Point,4326) | | |
lon | real | | |
lat | real | | |
uwnd | real | | |
vwnd | real | | |
angle | real | | |
speed | real | | |
reading_time | timestamp without time zone | | |
------------------------------------------------------------------------Log]-----------------------------------------
07 Oct 07:48:46 DEBUG [geoserver.security] - Request matched by universal pattern '/
07 Oct 07:48:46 DEBUG [geoserver.security] - Matched Path: /wfs, QueryString: service=wfs&version=2.0.0&request=GetFeature&typeName=windproc:windproc_view&outputFormat=application/json&CQL_FILTER=BBOX(geom%2C-12.875%2C26.125%2C42.125%2C55.125%2C%27EPSG%3A4326%27)%20AND%20reading_time%3D2018-05-27T12%3A00%3A00Z&srsname=EPSG:4326 with /

07 Oct 07:48:46 TRACE [ows.OWSHandlerMapping] - Mapped to HandlerExecutionChain with [org.geoserver.ows.Dispatcher@anonymised.com] and 2 interceptors
07 Oct 07:48:46 INFO [geoserver.wfs] -
Request: getServiceInfo
07 Oct 07:48:46 TRACE [geoserver.platform] - Found Property override: ‘org.geoserver.service.disabled’ to be unset
07 Oct 07:48:46 TRACE [geoserver.platform] - Found Java environment variable : ‘org.geoserver.service.disabled’ to be unset
07 Oct 07:48:46 TRACE [geoserver.platform] - Found Servlet context parameter : ‘org.geoserver.service.disabled’ to be unset
07 Oct 07:48:46 TRACE [geoserver.platform] - Found System environment variable : ‘org.geoserver.service.disabled’ to be unset
07 Oct 07:48:46 TRACE [geoserver.platform] - Found Property override: ‘org.geoserver.service.disabled’ to be unset
07 Oct 07:48:46 TRACE [geoserver.platform] - Found Java environment variable : ‘org.geoserver.service.disabled’ to be unset
07 Oct 07:48:46 TRACE [geoserver.platform] - Found Servlet context parameter : ‘org.geoserver.service.disabled’ to be unset
07 Oct 07:48:46 TRACE [geoserver.platform] - Found System environment variable : ‘org.geoserver.service.disabled’ to be unset
07 Oct 07:48:46 TRACE [geoserver.platform] - Found Property override: ‘org.geoserver.service.disabled’ to be unset
07 Oct 07:48:46 TRACE [geoserver.platform] - Found Java environment variable : ‘org.geoserver.service.disabled’ to be unset
07 Oct 07:48:46 TRACE [geoserver.platform] - Found Servlet context parameter : ‘org.geoserver.service.disabled’ to be unset
07 Oct 07:48:46 TRACE [geoserver.platform] - Found System environment variable : ‘org.geoserver.service.disabled’ to be unset
07 Oct 07:48:46 DEBUG [geoserver.requests] - Query is org.geoserver.wfs.request.Query$WFS20@anonymised.com
To gt2: Query:
feature type: windproc_view
filter: [[ geom bbox ReferencedEnvelope[-12.875 : 42.125, 26.125 : 55.125] DefaultGeographicCRS[EPSG:WGS 84] AXIS[“Geodetic longitude”, EAST] AXIS[“Geodetic latitude”, NORTH] ] AND [ reading_time = Sun May 27 13:00:00 BST 2018 ]]
[properties: ALL ]

On Sat, 7 Oct 2023 at 06:37, Dave Potts <mrdapotts@anonymised.com> wrote:

I am trying to make a cql request to a local instance of geoserver with a postgres backend
Requests such as
http://localhost:8090/geoserver/wfs?service=wfs&version=2.0.0&request=GetFeature&typeName=windproc:windproc_view&outputFormat=application/json&CQL_FILTER=BBOX(geom%2C-12.875%2C26.125%2C42.125%2C55.125%2C%27EPSG%3A4326%27)%20AND%20reading_time%3D2018-12-25T06%3A00%3A00Z&srsname=EPSG:4326
Work ie the date is 2018-12-25
Making a request such as
http://localhost:8090/geoserver/wfs?service=wfs&version=2.0.0&request=GetFeature&typeName=windproc:windproc_view&outputFormat=application/json&CQL_FILTER=BBOX(geom%2C-12.875%2C26.125%2C42.125%2C55.125%2C%27EPSG%3A4326%27)%20AND%20reading_time%3D2018-06-25T06%3A00%3A00Z&srsname=EPSG:4326
returns nothing!
A bit of digging in the logs and I can I can see that a request e date and time has been translated into a request for data from the database of using 2018-06-25 0700, since the database only has records for 0600 it does’nt find any data.

Basically geoserver is converting a date into daylight saving time format, and then making a request in the database.

Q. How do I tell the geoserver , not to convert a date in cql into a daylight saving version ?

I assume the issue is date format issue, I normally supply a date as something like

2018-12-25T06:00:00.000Z

Thanks in advance for any help.

Dave.

If you don’t specify a time zone in the filter then it is taken to be in the time zone the server is running, so either add the Z (UTC) marker in the query or add the -Duser.timezone=GMT flag to your java opts to set the server time zone to UTC/GMT

Ian

···

Ian Turton

Hmm, Trying the Z argument at the end of my time value did’nt work

But setting the timezone are per your suggestion worked !

I did have a good look at the documentation for geoserver and totally missed this startup argument, its a pity its not something listed global settings

But thank you for your prompt response, I can get on with my application now

···

Ian Turton