PostgreSQL 9.1 timezones

问题:

I'm using postgresql to store some dates on a database.
In my application it is fundamental that it is completely aware of timezones and I was conducting some basic tests, between, client, server and database.

I'm sending the dates from a browser's application I did in GWT and read the dates on postgresql.

My tests:

The client is always in GMT Timezone, and I'm sending always the same date for each case.
13/04/2012 00:00:00 GMT+00

On posgres I'm changing timezones for each test. Between tests I'm removing all dates from the table.

To change the timezone on the posgres, I do it on the {PostgreSQL HOME}\9.1\data\postgresql.conf setting the timezene to the one I want.

Tests: Client: 13/04/2012 00:00:00 GMT+00

1st test - posgres EST - 12/04/2012 19:00:00-05 According to postgresql documentation EST = GMT - 5
2nd test - posgres GMT + 5 - 12/04/2012 19:00:00-05
3rd test - posgres GMT - 5 - 13/04/2012 05:00:00+05

Now my question rises: According the the docs, EST = GMT - 5. So why am I reading it the other way around? Am I missing something here?

EDIT Technical aspects of my tests:
On the client I send this: 2012 Apr 13 00:00:00 GMT+00.
On the server I'm using JDBC to write on the db:
convert java.utils.date to java.sql.timestamp

java.sql.Timestamp sqlTimeStamp = new java.sql.Timestamp(date.getTime());
(date is java.utils.Date that comes from the client)

set the prepared statement
PreparedStatement ps = con.prepareStatement("INSERT INTO teste.dates (dates_tz, dates_ntz) VALUES (?, ?);"

ps.setTimestamp(1, sqlTimeStamp); ...

For the record, this is just something I want to understand, because overall it works well for my purposes..

回答1:

Consider that warning in the documentation:

Another issue to keep in mind is that in POSIX time zone names, positive offsets are used for locations west of Greenwich. Everywhere else, PostgreSQL follows the ISO-8601 convention that positive timezone offsets are east of Greenwich.

It looks like the opposite signs towards GMT you're seeing is exactly the effect of that divergence. The timezone specified in postgresql.conf is probably interpreted with POSIX rules, but it's later displayed by SQL with ISO-8601 rules (the one that anyone really uses).

回答2:

When the clock on the wall shows 2012-04-13 00:00 in a time zone '+5', the time in London (GMT or UTC) is 2012-04-12 19:00.

If you live in the USA, your local time zone may be '-5'. When it's midnight there, it is 05:00 in the morning in London.

That's just how it is. This detailed answer about handling of time zones in PostgreSQL may help you understand.