Postgis Date tests fail for some time zones

Description

When running Postgis online tests in a Vancouver Time zone, the testFiltersByDate test fails:

PostgisDateOnlineTest>OnlineTestCase.run:123->JDBCDateOnlineTest.testFiltersByDate:45 expected:<2> but was:<1>

The test case defines:
DateFormat df = new SimpleDateFormat("yyyy-dd-MM");
df.setTimeZone( TimeZone.getTimeZone("PST"));
Then, it constructs a query, calling df.parse("2009-28-06"), which returns a java.util.Date: "Sun Jun 28 00:00:00 PDT 2009" (Notice the timezone)
This query eventually has a simplifyingFilterVisitor called on it, which causes the date to be cast to a java.sql.Date: "2009-27-06"
Since sql dates have no time component, the time is truncated and due to the timezone results in the 27th.
This causes the filter to match only one date instead of two (as expected).

Environment

None

Activity

Show:
codehaus
April 10, 2015, 2:57 PM

CodeHaus Comment From: bencaradocdavies - Time: Wed, 18 Feb 2015 19:08:50 -0600
---------------------
Confirmed on postgres 9.4 by setting host timezone to Vancouver:

-------- Forwarded Message --------

Subject: Re: [Geotools-devel] GeoTools PostGIS Test failures

Date: Thu, 19 Feb 2015 12:27:13 +1300

From: Ben Caradoc-Davies &lt;<a href='ben@transient.nz'>ben@transient.nz</a>&gt;

To: Torben Barsballe &lt;<a href='tbarsballe@boundlessgeo.com'>tbarsballe@boundlessgeo.com</a>&gt;

CC: GeoTools Developers list &lt;<a href='geotools-devel@lists.sourceforge.net'>geotools-devel@lists.sourceforge.net</a>&gt;

Torben,

I can confirm that if I set my local timezone to America/Vancouver, I

get the following gt-jdbc-postgis failures:

Failed tests:

PostgisDateOnlineTest&gt;OnlineTestCase.run:123-&gt;JDBCDateOnlineTest.testFiltersByDate:45

expected:&lt;2&gt; but was:&lt;1&gt;

PostgisUDTOnlineTest&gt;OnlineTestCase.run:123-&gt;testRead:81

expected:&lt;2004-10-[30 17]:30:00.0&gt; but was:&lt;2004-10-[29 21]:30:00.0&gt;

PostgisDateOnlineTest&gt;OnlineTestCase.run:123-&gt;JDBCDateOnlineTest.testFiltersByDate:45

expected:&lt;2&gt; but was:&lt;1&gt;

Not sure about the middle one as it looks suspiciously close to the 21

hours between us. I restarted postgres but I did not change the time

zone of the test database, which is still set to NZ. Might need to get

these to match for the test to pass. I am using postgres 9.4 and the

default timezone setting changed in 9.something.

Have you created a Jira issue for this failure?

Kind regards,

Ben.

codehaus
April 10, 2015, 2:57 PM

CodeHaus Comment From: ianturton - Time: Thu, 19 Feb 2015 02:26:35 -0600
---------------------
This is related (I suspect) to <a href="https://jira.codehaus.org/browse/GEOT-4984">https://jira.codehaus.org/browse/GEOT-4984</a> - according to this discussion (<a href="http://osgeo-org.1560.x6.nabble.com/TemporalConvertor-bug-td5181521.html">http://osgeo-org.1560.x6.nabble.com/TemporalConvertor-bug-td5181521.html</a>) Jody fixed the issue by hard coding the time zone into the JDBC Base Test class.

codehaus
April 10, 2015, 2:57 PM

CodeHaus Comment From: aaime - Time: Sun, 22 Feb 2015 02:31:28 -0600
---------------------
Hi, I&#39;ve disabled range based simplification in the FilterSimplifier, which I believe was the cause of this and other potential issues. See <a href="http://jira.codehaus.org/browse/GEOT-5029">http://jira.codehaus.org/browse/GEOT-5029</a>

Assignee

Unassigned

Reporter

codehaus

Triage

None

Components

Affects versions

Priority

Low
Configure