Re: [BUGS] Timezone issues with Postrres
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. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Timezone issues with Postrres
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 alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Timezone issues with Postrres
On Thu, Sep 22, 2011 at 07:42, Tom Lane t...@sss.pgh.pa.us wrote: pratikchirania pratik.chira...@hp.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/ -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Timezone issues with Postrres
Magnus Hagander mag...@hagander.net writes: On Thu, Sep 22, 2011 at 07:42, Tom Lane t...@sss.pgh.pa.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 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Timezone issues with Postrres
Hi, Thanks for your response. I tried your suggestion and got the following outputs: C:\PostgreSQL\9.0\binpg_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\binpg_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\binpg_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. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Timezone issues with Postrres
On Wed, Sep 21, 2011 at 3:16 AM, pratikchirania pratik.chira...@hp.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 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Timezone issues with Postrres
On 21-09-2011 13:38, Robert Haas wrote: On Wed, Sep 21, 2011 at 3:16 AM, pratikchiraniapratik.chira...@hp.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 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Timezone issues with Postrres
Euler Taveira de Oliveira eu...@timbira.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 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Timezone issues with Postrres
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. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Timezone issues with Postrres
pratikchirania pratik.chira...@hp.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 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] 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. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Timezone issues with Postrres
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 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
