[OSM-dev] Fwd: Streaming Replication

[Brett Henderson]

On 15 October 2012 23:08, Matt Amos <zerebubuth at gmail.com> wrote:

> On Sun, 2012-10-14 at 22:39 +1100, Brett Henderson wrote:
> > The timestamp columns in the database are set to "timestamp without
> > time zone" which presumably means the timezones of dates aren't
> > automatically converted to the correct timezone upon querying.  I'm a
> > bit confused though because I believe PostgreSQL itself is running in
> > the BST timezone.  I'd like to investigate further but I don't have
> > time at the moment.
>
> bare timestamps are stored in the database in UTC always [1]. as far as
> i know, there's no way to decorate the column with this information, so
> postgres doesn't have it to do any conversions. it seems likely that
> somewhere along the stack of software doing the query something is
> adding timezone information by defaulting to the current timezone, and
> it shouldn't be doing that.
>
> cheers,
>
> matt
>
> [1]
>
> https://github.com/openstreetmap/openstreetmap-website/blob/master/app/models/node.rb#L290
>

Java and Ruby might operate differently in this regard.  There's no such
thing as timezone information on a Java date type, everything is
milliseconds since 1970.  However Java database drivers may choose to
formate dates in a local timezone prior to sending them to a database
server.  If I'm running in BST I suspect the date is presented to
PostgreSQL in BST format, but probably with a timezone suffix.  The
timestamp columns in the API database are all defined as "timestamp without
time zone" which I believe means that PostgreSQL will ignore any provided
timezone information [1] but it depends on how the driver is implemented.
If the the timestamp columns were defined as "timestamp with time zone"
then PostgreSQL would look at the timezone suffix and convert dates to UTC
before storing.  When reading dates from the database the reverse process
will occur.

In summary, given that Ruby appears to be storing dates in UTC and the
timestamp columns are defined as "timestamp without time zone", I'll have
to ensure all Java processes run in UTC in order for dates to be processed
correctly.

Disclaimer:
Everything I've written above is likely to be wrong :-)

[1] (Search for "silently ignore any")
http://www.postgresql.org/docs/9.2/static/datatype-datetime.html
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/dev/attachments/20121019/7ffbb821/attachment.html>