PostgreSQL has a several ways to store dates that might be problematic:
- timestamp without time zone
- date without a time of day.
http://www.postgresql.org/docs/9.4/static/datatype-datetime.html
Assuming we're limiting the scope to PostgreSQL with one of these column types:
(a) +1 for removing the "Z"
(b) +1 for remapping PostgreSQL date column to the new Java date classes:
- timestamp without time zone --> java.time.LocalTime - date without a time of
day --> java.time.LocalDate
From: Andreas Watermeyer <[email protected]>
To: Ian Turton <[email protected]>
Cc: geotools-devel <[email protected]>
Sent: Monday, December 21, 2015 6:36 AM
Subject: Re: [Geotools-devel] GEOT-5329: Proposal to avoid Date-shifting due
to Timezones
Hi all,
thank you very much for your response.
I did some more research on this:
Most important: I was wrong, dates can be time zoned:
http://www.w3.org/TR/xmlschema-2/#date. The XML spec for dates and times is
inspired by ISO 8601. Both, the XML spec and ISO 8601 allow the time zone
postfix "Z" for dates, according to http://www.cs.tut.fi/~jkorpela/iso8601.html.
On the other hand I am still convinced, that the vast majority of systems
require a time zone unaware date handling. This is also reflected by GEOT-5025,
GEOS-1235, GEOS-1161, GEOT-5025 (CITE tests fail depending on time zone).
My assumption is:
- either java.sql.Date users use GeoTools in an area with a timezone offset >
0, where the problem just does not exist
- the particular project has not even realized that dates are shifted by -1
But I can not imagine that anybody relies on the current implementation.
I currently see two possible solutions:
a) My proposal. But in this case the "Z" must be removed. Otherwise the date is
considered time zoned, which is wrong. Without "Z" it is considered to be
local, which would be correct.
b) Making the behaviour configurable (certainly the best choice). I dont know
very much of GeoTools. What would be the way to go?
I could image to split the problem in two parts:
1) Support Java 8 Time API. By definition java.time.LocalDate has no time zone.
It should be clear that such Date objects should be converted to strings
without time zone.
2) Let the user control if a PostGIS date column should be published as
java.sql.Date or java.time.LocalTime. I am unsure if JDBC can handle that out
of the box, although it seems so
(http://www.oracle.com/technetwork/articles/java/jf14-date-time-2125367.html)
I suppose that is not an easy solution and I am probably not the one to
implement it, having not enough GeoTools know-how.
What do you think?
BTW: Is it possible to run the CITE tests against GeoServer/GeoTools easily?
Maybe as part of the build? How?
Best regards,
Andreas
2015-12-19 12:28 GMT+01:00 Ian Turton <[email protected]>:
I'd hate to reopen this can of worms - feel free to see
https://github.com/geotools/geotools/pull/808 &
https://github.com/geotools/geotools/pull/872 &
https://github.com/geotools/geotools/pull/775 and probably more besides.
There is no good way to handle dates going into and out of the Databases we
support without either forcing an explicit timezone or converting them to
strings which makes filtering much harder.
Please be very careful if you do make any changes to make sure all the tests
pass as is.
Ian
On 19 December 2015 at 07:09, Andrea Aime <[email protected]> wrote:
Hi Jody,there is indeed a chance this change will break CITE testing, however,
weare running a old and buggy version of it.After six months of waiting for the
new engine to be deployed on ares I'vehonestly lost interest and gave up
(others feel free to resume the effort), but if we ever do get stuff on there,
this change should probably be revisited.
In particular, formatting the date with a Z ending it just seems out of
spec,dates do not seem to have a timezone
specifier:https://en.wikipedia.org/wiki/ISO_8601
Unless of course the wikipedia page misses some information comparedto the
actual full ISO8601 standard.
Now, to be honest, the notion of a date without a timezone is rather
counter-intuitive to me (probably because I'm too used to work across a large
span of timezones). Right now If you ask me what day it is, I'll say Saturday,
but if we ask Jody, he'll say Friday (it's 8amnow in italy, but 11pm of the day
before in Victoria, Canada). That is, to me, it does not make sense to talk
about a date without a notionof timezone... it would not even make sense to
have a thing called theinternational date line, a line that when crossed
changes the current_day_ : https://en.wikipedia.org/wiki/International_Date_Line
Citing a fitting example from that page:"Christmas, for example, is celebrated
on 25 December (according to either the Gregorian or the Julian calendar,
depending upon which of the two is used by the particular church) as that date
falls in countries located on either side of the Date Line. Thus, whether it is
Western Christmas or Orthodox Christmas, Christians in Samoa, immediately west
of the Date Line, will celebrate the holiday a day before Christians in
American Samoa, which is immediately east of the Date Line"
That said, I fully understand that storing a pure date in a database, andhave
it changed by the GML output to the day before/after isquite maddening, unless
someone is really working in an international,world wide, setting (at which
point, they probably want to be more preciseand use full timestamps)
Maybe we should have some sort of way to control this behavior?
CheersAndrea
On Sat, Dec 19, 2015 at 1:52 AM, Jody Garnett <[email protected]> wrote:
Hey Andreas, interesting discussion.
I just rejected a "workaround" (https://github.com/geotools/geotools/pull/997)
on a similar topic - dealing with dates far in the past.
We tend to base our datastore / query / filter API off the WFS - in particular
we may be able to check the OGC docs for clarification (or ask on the
[email protected] email list).
While your argument makes sense to me I would like to focus on interoperability
if we can.
--Jody Garnett
On 18 December 2015 at 03:44, Andreas Watermeyer
<[email protected]> wrote:
Hi GeoTools-Developers,
I recently created https://osgeo-org.atlassian.net/browse/GEOT-5329 to
propose a patch to change GeoTools java.sql.Date to String conversion
due to problems arising from timezones.
Andrea Aime suggested involve this mailing list.
Problem:
A date (without time) in Postgres (2015-12-18) is delivered to client
as 2015-12-17Z.
This occurs due to internal time zone conversion to GMT.
My understanding (from https://en.wikipedia.org/wiki/ISO_8601):
Dates (without time component) are not related to time zones. They are
unaware of time zones.
Additionally:
When talking about dates (without time), it does not matter, at which
*time* the day starts or ends, it is just a date (without time). That
means: People in Europe and Australia have same understanding of Jan
1st, but they have if different understand about when the day starts
or ends. But this is about times *not* about dates.
So, my suggestion is as follows:
A date (without time) should not be changed on transfer, no matter of
time zone boundaries.
Reason:
Dates are not time zone dependent. If somebody wants time zone
dependent behaviour, it is required to specify a date and time, not a
pure date.
In Java Objects:
Date (without time): java.sql.Date - shall be transferred unaware of time zones
Date (with time): java.util.Date - shall be transferred using time
zone shifting
The proposed patch implements this concept.
In other words:
I think it is very unlikely, that anybody expects a date (without
time) to be shifted, because of time zone difference. Especially in
cases of a small offset of +/- 1h.
The current behaviour implies that GeoTools consumers treat dates
(without time) as timezone-aware date and time objects, transfer the
date and time into the local time zone and treat it as date again.
Note:
Unfortunately java.sql.Date is internally time-based, which is an
often criticized design decision and leads to constant confusion. I
think this is the actual reason for this issue.
One further aspect:
GeoTools currently formats dates as "2015-12-18Z", "Z" for time zone
UTC ("Zulu").
As far as I know this is required to be CITE compliant. Due to
https://en.wikipedia.org/wiki/ISO_8601 this is not a valid format for
dates (without time). But maybe wikipedia is incomplete.
I am not suggesting to change this behaviour, although it make no
sense, in my opinion.
Technical Conclusion:
The patch is based on the assumption, that java.sql.Date objects
should be treated as date without time. Dates without time should not
be changed on transfer across time zone boundaries. Therefore
java.sql.Date shall be converted to a string reflecting the local
meaning. In the current implementation this means, that the time zone
offset has to be considered to neutralized time zone effects.
This is my point of view. I hoper the majority can follow my suggestion.
With best regards,
Andreas
----------------------------------------------------------------
ITS Telco Services GmbH
GIS Services & Solutions
Telecommunications Division
Dillenburger Str. 77
D-51105 Köln
Tel.: +49 (0)221 820 07 00
Fax : +49 (0)221 820 07 22
Mail: [email protected]
Web : http://www.its-telco.de
----------------------------------------------------------------
Sitz der Gesellschaft: Köln
Amtsgericht Köln, HRB 59310
Geschäftsführer: Gunnar Haack, Ralf Petersilka, Michael Schnepf, Kai
Schriewer, Ludger Schulte
----------------------------------------------------------------
Diese E-Mail enthält vertrauliche Informationen. Wenn Sie nicht der
richtige Adressat sind oder diese E-Mail irrtümlich erhalten haben,
informieren Sie bitte sofort den Absender und vernichten Sie diese
E-Mail. Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser
E-Mail ist nicht gestattet.
This e-mail may contain confidential information. If you are not the
intended recipient (or have received this e-mail in error) please
notify the sender immediately and destroy this e-mail. Any
unauthorised copying, disclosure or distribution of the material in
this e-mail is strictly forbidden.
------------------------------------------------------------------------------
_______________________________________________
GeoTools-Devel mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/geotools-devel
------------------------------------------------------------------------------
_______________________________________________
GeoTools-Devel mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/geotools-devel
--
==GeoServer Professional Services from the experts! Visithttp://goo.gl/it488V
for more information.==
Geosolutions' Winter Holidays from 24/12 to 6/1
Ing. Andrea Aime
@geowolfTechnical Lead
GeoSolutions S.A.S.Via Poggio alle Viti 118755054 Massarosa (LU)Italyphone:
+39 0584 962313fax: +39 0584 1660272mob: +39 339 8844549
http://www.geo-solutions.ithttp://twitter.com/geosolutions_it
AVVERTENZE AI SENSI DEL D.Lgs. 196/2003Le informazioni contenute in questo
messaggio di posta elettronica e/o nel/i file/s allegato/i sono da considerarsi
strettamente riservate. Il loro utilizzo è consentito esclusivamente al
destinatario del messaggio, per le finalità indicate nel messaggio stesso.
Qualora riceviate questo messaggio senza esserne il destinatario, Vi preghiamo
cortesemente di darcene notizia via e-mail e di procedere alla distruzione del
messaggio stesso, cancellandolo dal Vostro sistema. Conservare il messaggio
stesso, divulgarlo anche in parte, distribuirlo ad altri soggetti, copiarlo, od
utilizzarlo per finalità diverse, costituisce comportamento contrario ai
principi dettati dal D.Lgs. 196/2003. The information in this message and/or
attachments, is intended solely for the attention and use of the named
addressee(s) and may be confidential or proprietary in nature or covered by the
provisions of privacy act (Legislative Decree June, 30 2003, no.196 - Italy's
New Data Protection Code).Any use not in accord with its purpose, any
disclosure, reproduction, copying, distribution, or either dissemination,
either whole or partial, is strictly forbidden except previous formal approval
of the named addressee(s). If you are not the intended recipient, please
contact immediately the sender by telephone, fax or e-mail and delete the
information in this message that has been received in error. The sender does
not give any warranty or accept liability as the content, accuracy or
completeness of sent messages and accepts no responsibility for changes made
after they were sent or for other risks which arise as a result of e-mail
transmission, viruses, etc.
-------------------------------------------------------
------------------------------------------------------------------------------
_______________________________________________
GeoTools-Devel mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/geotools-devel
--
Ian Turton
--
Andreas Watermeyer
office: +49 (0)221 820 07 14
----------------------------------------------------------------
ITS Telco Services GmbH
GIS Services & Solutions
Telecommunications Division
Dillenburger Str. 77
D-51105 Köln
Tel.: +49 (0)221 820 07 00
Fax : +49 (0)221 820 07 22
Mail: [email protected]
Web : http://www.its-telco.de
----------------------------------------------------------------
Sitz der Gesellschaft: Köln
Amtsgericht Köln, HRB 59310
Geschäftsführer: Gunnar Haack, Ralf Petersilka, Michael Schnepf, Kai Schriewer,
Ludger Schulte
----------------------------------------------------------------
Diese E-Mail enthält vertrauliche Informationen. Wenn Sie nicht der richtige
Adressat sind oder diese E-Mail irrtümlich erhalten haben, informieren Sie
bitte sofort den Absender und vernichten Sie diese E-Mail. Das unerlaubte
Kopieren sowie die unbefugte Weitergabe dieser E-Mail ist nicht gestattet.
This e-mail may contain confidential information. If you are not the intended
recipient (or have received this e-mail in error) please notify the sender
immediately and destroy this e-mail. Any unauthorised copying, disclosure or
distribution of the material in this e-mail is strictly forbidden.
------------------------------------------------------------------------------
_______________________________________________
GeoTools-Devel mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/geotools-devel
------------------------------------------------------------------------------
_______________________________________________
GeoTools-Devel mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/geotools-devel
