Re: Timezone issues with Postrres

Hi,
I am experiencing the following anomaly while using postgres database:

Time is being interpreted incorrectly when I set time zone to UTC -6
(Central America).
Time shown when I query "SELECT NOW()" is 1 hour ahead of system time.
PS: Central america does not have daylight saving. That might not be the
issue.

Regards,
Pratik Chirania
Hewlett Packard

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Timezone-issues-with-Postrres-tp4809498p4809498.html
Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.

On 16-09-2011 01:57, pratikchirania wrote:
> Time is being interpreted incorrectly when I set time zone to UTC -6
> (Central America).
> Time shown when I query "SELECT NOW()" is 1 hour ahead of system time.
> PS: Central america does not have daylight saving. That might not be the
> issue.
>
It is not a bug. Was is the exact postgresql version? How old is your
installation? It seems your PostgreSQL or OS tzdata is not up to date. If
'pg_config --prefix' command has '--with-system-tzdata' update your
installation to the latest minor version (for example, 8.4 -> 8.4.8) else
update your OS tzdata and restart the PostgreSQL.

--
Euler Taveira de Oliveira - Timbira http://www.timbira.com.br/
PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

Hi,

Thanks for your response.
I tried your suggestion and got the following outputs:

C:\PostgreSQL\9.0\bin>pg_config
BINDIR = C:/POSTGR~1/9.0/bin
DOCDIR = C:/POSTGR~1/9.0/doc
HTMLDIR = C:/POSTGR~1/9.0/doc
INCLUDEDIR = C:/POSTGR~1/9.0/include
PKGINCLUDEDIR = C:/POSTGR~1/9.0/include
INCLUDEDIR-SERVER = C:/POSTGR~1/9.0/include/server
LIBDIR = C:/POSTGR~1/9.0/lib
PKGLIBDIR = C:/POSTGR~1/9.0/lib
LOCALEDIR = C:/POSTGR~1/9.0/share/locale
MANDIR = C:/PostgreSQL/9.0/man
SHAREDIR = C:/POSTGR~1/9.0/share
SYSCONFDIR = C:/PostgreSQL/9.0/etc
PGXS = C:/PostgreSQL/9.0/lib/pgxs/src/makefiles/pgxs.mk
CONFIGURE = --enable-thread-safety --enable-integer-datetimes --enable-nls
--wit
h-ldap --with-ossp-uuid --with-libxml --with-libxslt --with-tcl --with-perl
--wi
th-python
VERSION = PostgreSQL 9.0.1

C:\PostgreSQL\9.0\bin>pg_config --prefix
pg_config: invalid argument: --prefix
Try "pg_config --help" for more information.

I have postgres version 9.0.1:
C:\PostgreSQL\9.0\bin>pg_config --version
PostgreSQL 9.0.1

The command clearly does not return '--with-system-tzdata'. I am using
Windows server 2008 R2. The TZ data must be working fine as other
applications on the OS are working fine. The issue is also reproducible on
Postgre version 8.3.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Timezone-issues-with-Postgres-tp4809498p4825401.html
Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.

On Wed, Sep 21, 2011 at 3:16 AM, pratikchirania <pratik(dot)chirania(at)hp(dot)com> wrote:
> The command clearly does not return '--with-system-tzdata'. I am using
> Windows server 2008 R2. The TZ data must be working fine as other
> applications on the OS are working fine. The issue is also reproducible on
> Postgre version 8.3.

You said that your PostgreSQL time zone was set to UTC-6. Are you
sure that's the case? What's the output from 'SHOW timezone'? Also,
what's the system time zone set to?

The reason I ask is because, for me, setting the time zone to UTC-6
gives me a time that is six hours AHEAD of UTC, which wouldn't be
appropriate for South America:

rhaas=# select now() at time zone 'utc', now() at time zone 'utc -6';
timezone | timezone
----------------------------+----------------------------
2011-09-21 16:31:26.082048 | 2011-09-21 22:31:26.082048
(1 row)

The rules for interpreting time zone specifications are arcane enough
to make me suspect that this isn't a bug even though it seems rather
odd, but in any case it would be useful to know how many hours
PostgreSQL's timestamp is behind (or ahead of) UTC and similarly for
the operating system.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 21-09-2011 13:38, Robert Haas wrote:
> On Wed, Sep 21, 2011 at 3:16 AM, pratikchirania<pratik(dot)chirania(at)hp(dot)com> wrote:
>> The command clearly does not return '--with-system-tzdata'. I am using
>> Windows server 2008 R2. The TZ data must be working fine as other
>> applications on the OS are working fine. The issue is also reproducible on
>> Postgre version 8.3.
>
> You said that your PostgreSQL time zone was set to UTC-6. Are you
> sure that's the case? What's the output from 'SHOW timezone'? Also,
> what's the system time zone set to?
>
> The reason I ask is because, for me, setting the time zone to UTC-6
> gives me a time that is six hours AHEAD of UTC, which wouldn't be
> appropriate for South America:
>
> rhaas=# select now() at time zone 'utc', now() at time zone 'utc -6';
> timezone | timezone
> ----------------------------+----------------------------
> 2011-09-21 16:31:26.082048 | 2011-09-21 22:31:26.082048
> (1 row)
>
That's odd because there is no 'utc -6' timezone. Moreover, 'utc+6' [1] should
be 6 hours ahead 'utc'. I don't read the code to confirm if it is a bug or a
correct behavior (as I don't understand much about the insane timezone rules).

> The rules for interpreting time zone specifications are arcane enough
> to make me suspect that this isn't a bug even though it seems rather
> odd, but in any case it would be useful to know how many hours
> PostgreSQL's timestamp is behind (or ahead of) UTC and similarly for
> the operating system.
>
I think the OP is talking about one of these timezones:

euler=# select * from pg_timezone_names where utc_offset = '-06:00:00';
name | abbrev | utc_offset | is_dst
--------------------------+--------+------------+--------
Mexico/BajaSur | MDT | -06:00:00 | t
Pacific/Galapagos | GALT | -06:00:00 | f
US/Mountain | MDT | -06:00:00 | t
Canada/Mountain | MDT | -06:00:00 | t
Canada/Saskatchewan | CST | -06:00:00 | f
Canada/East-Saskatchewan | CST | -06:00:00 | f
America/Swift_Current | CST | -06:00:00 | f
America/Denver | MDT | -06:00:00 | t
America/Chihuahua | MDT | -06:00:00 | t
America/Belize | CST | -06:00:00 | f
America/Costa_Rica | CST | -06:00:00 | f
America/Shiprock | MDT | -06:00:00 | t
America/Managua | CST | -06:00:00 | f
America/Tegucigalpa | CST | -06:00:00 | f
America/Guatemala | CST | -06:00:00 | f
America/Cambridge_Bay | MDT | -06:00:00 | t
America/Regina | CST | -06:00:00 | f
America/Ojinaga | MDT | -06:00:00 | t
America/Yellowknife | MDT | -06:00:00 | t
America/El_Salvador | CST | -06:00:00 | f
America/Edmonton | MDT | -06:00:00 | t
America/Mazatlan | MDT | -06:00:00 | t
America/Boise | MDT | -06:00:00 | t
America/Inuvik | MDT | -06:00:00 | t
MST7MDT | MDT | -06:00:00 | t
Navajo | MDT | -06:00:00 | t
Etc/GMT+6 | GMT+6 | -06:00:00 | f
(27 registros)

... and I suspect the is_dst is true.

[1] http://en.wikipedia.org/wiki/UTC%2B6

--
Euler Taveira de Oliveira - Timbira http://www.timbira.com.br/
PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

Euler Taveira de Oliveira <euler(at)timbira(dot)com> writes:
> On 21-09-2011 13:38, Robert Haas wrote:
>> The rules for interpreting time zone specifications are arcane enough
>> to make me suspect that this isn't a bug even though it seems rather
>> odd, but in any case it would be useful to know how many hours
>> PostgreSQL's timestamp is behind (or ahead of) UTC and similarly for
>> the operating system.

> I think the OP is talking about one of these timezones:

It's a bit premature to speculate without knowing his exact timezone
setting, but there seem at least three possibilities:

1. The system clock is, in fact, set wrong, so that the OS is delivering
the wrong UTC time to Postgres. This being on a Windows platform, I
wouldn't write that off. It would be a good idea to do
SET TIMEZONE = UTC;
and then see if now() reports the correct UTC time.

2. The timezone setting he's using is inappropriate for the jurisdiction
he's in, so that Postgres is following the wrong DST rule. Not knowing
either his actual setting or his precise jurisdiction, this is hard to
guess about.

3. The zone data that Postgres has is obsolete for his zone. This seems
entirely possible, although a look at the git logs doesn't reveal any
changes in Central American zone rules since 9.0.1 was released. (I see
a change in Mexican rules listed for tzdata release 2010j in May 2010,
but that was in 9.0 beta2 and later.) A relevant question here is
whether his jurisdiction has observed DST in recent years and then
changed their laws.

regards, tom lane

Hi, thanks for the responses. Here are updates from my end:

1. I am in Costa Rica, using Windows Server 2008 R2/Windows 2003 with
PostgreSQL 8.3/9.0

2. System Date/Time Settings shows "CST/Central America" with UTC-6 as extra
display

system timezone: (using command: systeminfo)
Time Zone: (UTC-06:00) Central America

3. other applications are showing the correct time (i.e., the System Time
setting IS correct as can be confirmed by a time checker such as
http://www.timeanddate.com/worldclock/)

4. There is NO DST for CST (Central America) a.k.a America/Costa_Rica to the
PostgreSQL database through the pg_timezone_names view

5. If the America/Costa_Rica entry is made in the posgresql.conf file
(timezone field), it works as expected and matches the System TimeZone/Date
and Time

Here are the results you had requested for:

show timezone
"CST6CDT"

select now() at time zone 'utc', now() at time zone 'utc -6', NOW();

"2011-09-22 02:50:49.746"; "2011-09-22 08:50:49.746"; "2011-09-21
21:50:49.746-05"

regards,
Pratik

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Timezone-issues-with-Postgres-tp4809498p4828973.html
Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.

pratikchirania <pratik(dot)chirania(at)hp(dot)com> writes:
> 1. I am in Costa Rica, using Windows Server 2008 R2/Windows 2003 with
> PostgreSQL 8.3/9.0
> 2. System Date/Time Settings shows "CST/Central America" with UTC-6 as extra
> display
> 4. There is NO DST for CST (Central America) a.k.a America/Costa_Rica to the
> PostgreSQL database through the pg_timezone_names view

Well, if you want no-DST behavior, this is wrong:

> show timezone
> "CST6CDT"

That timezone specifies daylight savings behavior (CDT). As it happens,
it's going to follow the USA rules for when to switch, but any switch
would be wrong for Costa Rica. You need the America/Costa_Rica setting.

Now having said that, it appears that the reason you got "CST6CDT" by
default is that we map the Windows "Central America Standard Time" and
"Central America Daylight Time" registry strings to that. This seems
clearly wrong. A look at the tzdata "northamerica" file shows that
noplace in Central America other than Mexico has observed DST with any
regularity, and certainly none of them could be said to follow USA DST
rules.

Mexico is a separate case, because there are separate "Central Standard
Time (Mexico)" and "Central Daylight Time (Mexico)" entries, which we
map to "America/Mexico_City", which seems proper.

I think we ought to map "Central America Standard Time" to plain CST6.
(Or we could map to one of America/Costa_Rica, America/Guatemala,
America/El_Salvador, etc, but that seems more likely to offend people in
the other countries than provide any additional precision.) I am not
sure what we ought to do with "Central America Daylight Time", but on
the evidence here I wonder whether that setting exists in the wild at
all.

Magnus, AFAICT from the commit logs, that lookup table was your work to
begin with --- do you remember anything about the reasoning for the
Central America entries?

regards, tom lane

On Thu, Sep 22, 2011 at 07:42, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> pratikchirania <pratik(dot)chirania(at)hp(dot)com> writes:
>> 1. I am in Costa Rica, using Windows Server 2008 R2/Windows 2003 with
>> PostgreSQL 8.3/9.0
>> 2. System Date/Time Settings shows "CST/Central America" with UTC-6 as extra
>> display
>> 4. There is NO DST for CST (Central America) a.k.a America/Costa_Rica to the
>> PostgreSQL database through the pg_timezone_names view
>
> Well, if you want no-DST behavior, this is wrong:
>
>> show timezone
>> "CST6CDT"
>
> That timezone specifies daylight savings behavior (CDT).  As it happens,
> it's going to follow the USA rules for when to switch, but any switch
> would be wrong for Costa Rica.  You need the America/Costa_Rica setting.
>
> Now having said that, it appears that the reason you got "CST6CDT" by
> default is that we map the Windows "Central America Standard Time" and
> "Central America Daylight Time" registry strings to that.  This seems
> clearly wrong.  A look at the tzdata "northamerica" file shows that
> noplace in Central America other than Mexico has observed DST with any
> regularity, and certainly none of them could be said to follow USA DST
> rules.
>
> Mexico is a separate case, because there are separate "Central Standard
> Time (Mexico)" and "Central Daylight Time (Mexico)" entries, which we
> map to "America/Mexico_City", which seems proper.
>
> I think we ought to map "Central America Standard Time" to plain CST6.
> (Or we could map to one of America/Costa_Rica, America/Guatemala,
> America/El_Salvador, etc, but that seems more likely to offend people in
> the other countries than provide any additional precision.)  I am not
> sure what we ought to do with "Central America Daylight Time", but on
> the evidence here I wonder whether that setting exists in the wild at
> all.
>
> Magnus, AFAICT from the commit logs, that lookup table was your work to
> begin with --- do you remember anything about the reasoning for the
> Central America entries?

Hmm. not entirely. I know the initial round was basically all just
guesses. Then at some point we added tools/win32tzlist.pl. But IIRC
the actual timezones picked were more or less still guestimates. So I
think it's just a mistake in that, and should be changed.

--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Magnus Hagander <magnus(at)hagander(dot)net> writes:
> On Thu, Sep 22, 2011 at 07:42, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> I think we ought to map "Central America Standard Time" to plain CST6.
>>
>> Magnus, AFAICT from the commit logs, that lookup table was your work to
>> begin with --- do you remember anything about the reasoning for the
>> Central America entries?

> Hmm. not entirely. I know the initial round was basically all just
> guesses. Then at some point we added tools/win32tzlist.pl. But IIRC
> the actual timezones picked were more or less still guestimates. So I
> think it's just a mistake in that, and should be changed.

OK, done.

regards, tom lane

Hi,

Thanks for the replies and confirmation.
Can you provide me with any defect number or some equivalent for tracking
purpose?

Regards, Pratik

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Timezone-issues-with-Postgres-tp4809498p4844991.html
Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.

Excerpts from pratikchirania's message of mar sep 27 08:22:45 -0300 2011:
> Hi,
>
> Thanks for the replies and confirmation.
> Can you provide me with any defect number or some equivalent for tracking
> purpose?

Hmm, this was fixed in the master Git branch (what's going to become 9.2
eventually) but not backpatched to 9.0.

http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=4c5d837e69cf92e906acfa3000d848d4524beee9

You should probably grab the patch, apply locally, and recompile.

--
Álvaro Herrera <alvherre(at)commandprompt(dot)com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support