Thread: 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.chirania@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
On 21-09-2011 13:38, Robert Haas wrote: > On Wed, Sep 21, 2011 at 3:16 AM, pratikchirania<pratik.chirania@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
Euler Taveira de Oliveira <euler@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
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.chirania@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
On Thu, Sep 22, 2011 at 07:42, Tom Lane <tgl@sss.pgh.pa.us> wrote: > pratikchirania <pratik.chirania@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 e= xtra >> 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). =A0As it happens, > it's going to follow the USA rules for when to switch, but any switch > would be wrong for Costa Rica. =A0You 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. =A0This seems > clearly wrong. =A0A 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.) =A0I 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. --=20 =A0Magnus Hagander =A0Me: http://www.hagander.net/ =A0Work: http://www.redpill-linpro.com/
Magnus Hagander <magnus@hagander.net> writes: > On Thu, Sep 22, 2011 at 07:42, Tom Lane <tgl@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
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@commandprompt.com> The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support