Hi all, I am requesting some clarification on how geotools converts CQL date/time filters to an SQL timestamp...
geotools version 14.0 (running in geoserver 2.8.0), postgresql 9.3, server timezone 'Australia/Hobart' (+10:00) Consider, a CQL filter such as: time = '2014-06-06T00:30:00Z' will be converted to SQL: time = '2014-06-06 10:30:00.0' and likewise, CQL: time = '2014-06-06T00:30:00 # no trailing 'Z' converts to: time = '2014-06-06 00:30:00.0' The problem, at least the way I see it, is that the SQL timestamp never includes the timezone information, meaning that postgresql will interpret the timestamp as *local time*. It is stored in a *timestamp with timezone* column, with values in UTC. So, I have discrepancy which is causing incorrect results to be returned. I can workaround this by setting the server timezone to 'UTC', or run geoserver with property "-Duser.timezone=UTC', but this doesn't seem like the correct solution to me (as we're starting to introduce assumptions which may not always hold true). Is there any reason why geotools is not generating SQL *including* timezone info if it's given in the original CQL filter? e.g. if the converted values above were '2014-06-06 10:30:00.0+10:00' and '2014-06-06 00:30:00.0Z' respectively, then I *think* that would work reliably for me. Cheers Jon Burgess IMOS
------------------------------------------------------------------------------
_______________________________________________ GeoTools-GT2-Users mailing list GeoTools-GT2-Users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/geotools-gt2-users
