[osmosis-dev] osmosis loading, apidb and time zones

[Brett Henderson]

On 18 July 2013 15:11, Paul Norman <penorman at mac.com> wrote:

> I've been working on some API tests, and ran into a case where the
> timestamps of objects in a file are not reproduced after loading the file
> into the database and fetching it with API calls.
>
> The file contains this node:
>
> <node id="1001" version="1" changeset="1001" lat="1.001" lon="1.001"
> user="user_1001" uid="1001" timestamp="2012-10-01T00:00:00Z"/>
>
> I load the file with osmosis into an apidb database and fetch it with the
> rails port and get back a node with timestamp="2012-09-30T17:00:00Z"
>
> If I go into the database and SELECT the timestamp column for the node I
> get
>
>
> 2012-09-30 17:00:00
>
> It looks like osmosis is doing an incorrect time zone conversion.
>
> I'm not sure where to ticket this.
>
> Note: I am in Vancouver, in the PDT timezone.
>

Timezones are my second favourite topic after character set encodings ;-)

I don't know if Osmosis is at fault here or not.  All of the apidb
timestamp columns are defined as "timestamp without time zone".  Apparently
this means that no timezone conversions are done during input and output
[1] (ie. the database ignores any supplied timezone information, as opposed
to "with time zone" which always stores in UTC).  I've checked the code and
I can't see anywhere where Osmosis doing anything special with timezones.
Osmosis converts a standard Java java.util.Date into a java.sql.Timestamp
for persistence.  Both of those types have an internal time field
containing the number of milliseconds since 1970.  java.util.Date always
stores this time field in UTC.  It is possible that the JDBC drivers are
passing the timestamp to the database in a UTC timezone and the database is
ignoring it.

In your example, your time is 7 hours off.  Are you in daylight savings
time at the moment?  If that's the case it certainly seems like UTC/PDT
confusion.

My maths might be wrong, but I would have thought that if the JDBC drivers
were passing in UTC times that the times would end up 7 hours ahead, not
behind.  So my above explanation might be wrong.

I'm not sure how to diagnose this one.  Perhaps trace queries against the
database server and see what Osmosis is passing in?

I'm also not sure why the schema doesn't use "timestamp with time zone".
It should make things a lot easier and allow all components to do automatic
timezone conversion as necessary.  I seem to recall having similar issues
on the main OSM servers.  I can't remember what the final fix was there,
but usually I aim to have everything running in UTC to avoid these issues.

[1] http://www.postgresql.org/docs/9.2/static/datatype-datetime.html
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/osmosis-dev/attachments/20130720/b9535812/attachment.html>