[Geoserver-users] CQL and Time queries

GeoServer GeoServer User
1

Hello,

I am having strange results whenever I do time filters with similar to the CQL:

intime > dateParse(‘yyyy-MM-dd HH:mm:ss’,‘2011-03-11 22:23:31’) and intime < dateParse(‘yyyy-MM-dd HH:mm:ss’,‘2011-03-11 22:58:51’)

wherein I would get features that are in the ‘2011-03-11 13:00:00’ time range. I tried also the BETWEEN … AND keywords and got similar strange results.

Is there a proper way of writing CQL filters with timestamps beside the one I am doing?

Thanks.

Mario.

2

Hi Mario,

From a quick peek at the docs for ECQL(*), you can specify a ‘date-time’ by giving the date, the letter ‘T’, and then the UTC time.

Example filters:
dtg BETWEEN ‘0000-01-01T00:00:00.000Z’ AND ‘9999-12-31T23:59:59.000Z’
or
dtg DURING 2010-08-08T00:00:00.000Z/2010-08-08T23:59:59.000Z

By chance is your timezone 9 hours off of UTC? The code for dateParse delegates to SimpleDateFormat.parse here: https://github.com/geotools/geotools/blob/master/modules/library/main/src/main/java/org/geotools/filter/function/FilterFunction_dateParse.java#L73-75.

Cheers,

Jim

···

On 06/13/2015 12:12 AM, Mario Basa wrote:

Hello,

I am having strange results whenever I do time filters with similar to the CQL:

intime > dateParse(‘yyyy-MM-dd HH:mm:ss’,‘2011-03-11 22:23:31’) and intime < dateParse(‘yyyy-MM-dd HH:mm:ss’,‘2011-03-11 22:58:51’)

wherein I would get features that are in the ‘2011-03-11 13:00:00’ time range. I tried also the BETWEEN … AND keywords and got similar strange results.

Is there a proper way of writing CQL filters with timestamps beside the one I am doing?

Thanks.

Mario.

------------------------------------------------------------------------------


_______________________________________________
Geoserver-users mailing list
[Geoserver-users@lists.sourceforge.net](mailto:Geoserver-users@lists.sourceforge.net)
[https://lists.sourceforge.net/lists/listinfo/geoserver-users](https://lists.sourceforge.net/lists/listinfo/geoserver-users)
3

Thank you very much Jim.

Yes, I am in Tokyo right now so it is UTC/GMT +9 for us here.

For some reason, this works:

intime BETWEEN ‘2011/03/11 23:01:26’ and ‘2011/03/11 23:09:12’

and returns the correct desired results even without the ‘Z’.

Still can not understand why this gives the wrong results:

intime BETWEEN ‘2011-03-11 23:01:26’ and ‘2011-03-11 23:09:12’

Mario.

···

On Mon, Jun 15, 2015 at 3:21 AM, Jim Hughes <jnh5y@anonymised.com> wrote:

Hi Mario,

From a quick peek at the docs for ECQL(*), you can specify a ‘date-time’ by giving the date, the letter ‘T’, and then the UTC time.

Example filters:
dtg BETWEEN ‘0000-01-01T00:00:00.000Z’ AND ‘9999-12-31T23:59:59.000Z’
or
dtg DURING 2010-08-08T00:00:00.000Z/2010-08-08T23:59:59.000Z

By chance is your timezone 9 hours off of UTC? The code for dateParse delegates to SimpleDateFormat.parse here: https://github.com/geotools/geotools/blob/master/modules/library/main/src/main/java/org/geotools/filter/function/FilterFunction_dateParse.java#L73-75.

Cheers,

Jim

On 06/13/2015 12:12 AM, Mario Basa wrote:

Hello,

I am having strange results whenever I do time filters with similar to the CQL:

intime > dateParse(‘yyyy-MM-dd HH:mm:ss’,‘2011-03-11 22:23:31’) and intime < dateParse(‘yyyy-MM-dd HH:mm:ss’,‘2011-03-11 22:58:51’)

wherein I would get features that are in the ‘2011-03-11 13:00:00’ time range. I tried also the BETWEEN … AND keywords and got similar strange results.

Is there a proper way of writing CQL filters with timestamps beside the one I am doing?

Thanks.

Mario.

------------------------------------------------------------------------------


_______________________________________________
Geoserver-users mailing list
[Geoserver-users@lists.sourceforge.net](mailto:Geoserver-users@lists.sourceforge.net)
[https://lists.sourceforge.net/lists/listinfo/geoserver-users](https://lists.sourceforge.net/lists/listinfo/geoserver-users)

Geoserver-users mailing list
Geoserver-users@anonymised.comsts.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users

4

Hi Mario,

As a quick guess, I’d suppose that those two date strings are being parsed by slightly different code. If someone knows for sure, they can chime in.

I might be able to look at it some more later. At the minute, do you have a sufficient work-around/understanding to make progress?

Cheers,

Jim

···

On 06/15/2015 11:53 AM, Mario Basa wrote:

Thank you very much Jim.

Yes, I am in Tokyo right now so it is UTC/GMT +9 for us here.

For some reason, this works:

intime BETWEEN ‘2011/03/11 23:01:26’ and ‘2011/03/11 23:09:12’

and returns the correct desired results even without the ‘Z’.

Still can not understand why this gives the wrong results:

intime BETWEEN ‘2011-03-11 23:01:26’ and ‘2011-03-11 23:09:12’

Mario.

On Mon, Jun 15, 2015 at 3:21 AM, Jim Hughes <jnh5y@anonymised.com> wrote:

Hi Mario,

From a quick peek at the docs for ECQL(*), you can specify a ‘date-time’ by giving the date, the letter ‘T’, and then the UTC time.

Example filters:
dtg BETWEEN ‘0000-01-01T00:00:00.000Z’ AND ‘9999-12-31T23:59:59.000Z’
or
dtg DURING 2010-08-08T00:00:00.000Z/2010-08-08T23:59:59.000Z

By chance is your timezone 9 hours off of UTC? The code for dateParse delegates to SimpleDateFormat.parse here: https://github.com/geotools/geotools/blob/master/modules/library/main/src/main/java/org/geotools/filter/function/FilterFunction_dateParse.java#L73-75.

Cheers,

Jim

On 06/13/2015 12:12 AM, Mario Basa wrote:

Hello,

I am having strange results whenever I do time filters with similar to the CQL:

intime > dateParse(‘yyyy-MM-dd HH:mm:ss’,‘2011-03-11 22:23:31’) and intime < dateParse(‘yyyy-MM-dd HH:mm:ss’,‘2011-03-11 22:58:51’)

wherein I would get features that are in the ‘2011-03-11 13:00:00’ time range. I tried also the BETWEEN … AND keywords and got similar strange results.

Is there a proper way of writing CQL filters with timestamps beside the one I am doing?

Thanks.

Mario.

------------------------------------------------------------------------------


_______________________________________________
Geoserver-users mailing list
[Geoserver-users@lists.sourceforge.net](mailto:Geoserver-users@lists.sourceforge.net)
[https://lists.sourceforge.net/lists/listinfo/geoserver-users](https://lists.sourceforge.net/lists/listinfo/geoserver-users)

Geoserver-users mailing list
Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users

5

Hello Jim,

Yes, I am getting the correct results with the little work-around.

Thanks a lot.

Mario.

···

On Tue, Jun 16, 2015 at 1:29 AM, Jim Hughes <jnh5y@anonymised.com> wrote:

Hi Mario,

As a quick guess, I’d suppose that those two date strings are being parsed by slightly different code. If someone knows for sure, they can chime in.

I might be able to look at it some more later. At the minute, do you have a sufficient work-around/understanding to make progress?

Cheers,

Jim

On 06/15/2015 11:53 AM, Mario Basa wrote:

Thank you very much Jim.

Yes, I am in Tokyo right now so it is UTC/GMT +9 for us here.

For some reason, this works:

intime BETWEEN ‘2011/03/11 23:01:26’ and ‘2011/03/11 23:09:12’

and returns the correct desired results even without the ‘Z’.

Still can not understand why this gives the wrong results:

intime BETWEEN ‘2011-03-11 23:01:26’ and ‘2011-03-11 23:09:12’

Mario.

On Mon, Jun 15, 2015 at 3:21 AM, Jim Hughes <jnh5y@anonymised.com> wrote:

Hi Mario,

From a quick peek at the docs for ECQL(*), you can specify a ‘date-time’ by giving the date, the letter ‘T’, and then the UTC time.

Example filters:
dtg BETWEEN ‘0000-01-01T00:00:00.000Z’ AND ‘9999-12-31T23:59:59.000Z’
or
dtg DURING 2010-08-08T00:00:00.000Z/2010-08-08T23:59:59.000Z

By chance is your timezone 9 hours off of UTC? The code for dateParse delegates to SimpleDateFormat.parse here: https://github.com/geotools/geotools/blob/master/modules/library/main/src/main/java/org/geotools/filter/function/FilterFunction_dateParse.java#L73-75.

Cheers,

Jim

On 06/13/2015 12:12 AM, Mario Basa wrote:

Hello,

I am having strange results whenever I do time filters with similar to the CQL:

intime > dateParse(‘yyyy-MM-dd HH:mm:ss’,‘2011-03-11 22:23:31’) and intime < dateParse(‘yyyy-MM-dd HH:mm:ss’,‘2011-03-11 22:58:51’)

wherein I would get features that are in the ‘2011-03-11 13:00:00’ time range. I tried also the BETWEEN … AND keywords and got similar strange results.

Is there a proper way of writing CQL filters with timestamps beside the one I am doing?

Thanks.

Mario.

------------------------------------------------------------------------------


_______________________________________________
Geoserver-users mailing list
[Geoserver-users@lists.sourceforge.net](mailto:Geoserver-users@lists.sourceforge.net)
[https://lists.sourceforge.net/lists/listinfo/geoserver-users](https://lists.sourceforge.net/lists/listinfo/geoserver-users)

Geoserver-users mailing list
Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users