PostgreSQL 9.1时区 [英] PostgreSQL 9.1 timezones

问题描述

我正在使用PostgreSQL在数据库上存储一些日期.
在我的应用程序中，基本的一点是要完全了解时区，并且我在客户端，服务器和数据库之间进行了一些基本测试.

我正在从我在GWT中执行的浏览器应用程序发送日期，并在postgresql上读取日期.

我的测试:

客户始终处于格林尼治标准时间(GMT)时区，对于每种情况，我总是发送相同的日期.
2012年4月13日00:00:00 GMT + 00

在posgres上，我正在更改每个测试的时区.在两次测试之间，我将从表格中删除所有日期.

要更改posgres上的时区，请在{PostgreSQL HOME} \ 9.1 \ data \ postgresql.conf上进行设置，将timezene设置为所需的时区.

测试: 客户:13/04/2012 00:00:00 GMT + 00

第一次测试-posgres EST-2012年12月4日19:00:00-05 根据 postgresql文档 EST = GMT-5
第二项测试-posgres GMT + 5-12/04/2012 19:00:00-05
第三次测试-posgres GMT-5-13/04/2012 05:00:00 + 05

现在我的问题浮出水面了:根据文档，EST = GMT-5.那为什么我要反过来阅读呢? 我在这里想念什么吗?

编辑 我的测试的技术方面:
在客户端上，我发送给您:2012年4月13日00:00:00 GMT + 00.
在服务器上，我正在使用JDBC在db上编写:
将java.utils.date转换为 java.sql.timestamp

java.sql.Timestamp sqlTimeStamp = new java.sql.Timestamp(date.getTime());
(日期是来自客户端的java.utils.Date)

设置准备好的语句
PreparedStatement ps = con.prepareStatement("INSERT INTO teste.dates (dates_tz, dates_ntz) VALUES (?, ?);"

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

出于记录目的，这只是我想了解的内容，因为总体而言，它可以很好地满足我的目的.

解决方案

请考虑文档:

要记住的另一个问题是，在POSIX时区名称中， 正偏移量用于格林威治以西的位置.到处 否则，PostgreSQL遵循ISO-8601约定， 时区偏移量位于格林威治以东.

看来，您所看到的与格林威治标准时间相反的迹象正是这种分歧的结果. 在postgresql.conf中指定的时区可能是用POSIX规则解释的，但后来由SQL使用ISO-8601规则(任何人真正使用的规则)显示.

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..

解决方案

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).

这篇关于PostgreSQL 9.1时区的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！