Thread: BUG #3948: date/time functions returning wrong value
The following bug has been logged online: Bug reference: 3948 Logged by: Jorge Campins Email address: jrcampins@cantv.net PostgreSQL version: 8.1 Operating system: Windows XP Description: date/time functions returning wrong value Details: As you might be aware, recently Venezuela has changed his timezone from GMT -4 to GMT -4:30. Windows implemented a new timezone for us, "Caracas", to be used instead of "Caracas/La Paz". As soon as I changed my computer's timezone to "Caracas", PostgreSQL date/time functions such as current_timestamp and localtimestamp started to return a wrong value (I guess they don't recognize the new timezone so they are returning GMT). Then I change it to "La Paz", and once again they returned my computer's time, but of course it has half an hour difference. I realize this is not really a bug. We will really appreciate your effort to solve this problem at your earliest convenience.
Jorge Campins wrote: > The following bug has been logged online: > > Bug reference: 3948 > Logged by: Jorge Campins > Email address: jrcampins@cantv.net > PostgreSQL version: 8.1 > Operating system: Windows XP > Description: date/time functions returning wrong value > Details: > > As you might be aware, recently Venezuela has changed his timezone from GMT > -4 to GMT -4:30. Windows implemented a new timezone for us, "Caracas", to be > used instead of "Caracas/La Paz". > > As soon as I changed my computer's timezone to "Caracas", PostgreSQL > date/time functions such as current_timestamp and localtimestamp started to > return a wrong value (I guess they don't recognize the new timezone so they > are returning GMT). > > Then I change it to "La Paz", and once again they returned my computer's > time, but of course it has half an hour difference. > > I realize this is not really a bug. We will really appreciate your effort to > solve this problem at your earliest convenience. Try setting your timezone in postgresql.conf - that should override whatever Windows has for you. //Magnus
Magnus Hagander <magnus@hagander.net> writes:
> Try setting your timezone in postgresql.conf - that should override
> whatever Windows has for you.
Unless his "8.1" is really 8.1.something-pretty-darn-recent, it's
not going to know about the latest Venezuela DST law change anyway.
This does suggest that we'll need to revisit the win32_tzmap[] list
every so often?
regards, tom lane
Tom Lane wrote: > Magnus Hagander <magnus@hagander.net> writes: >> Try setting your timezone in postgresql.conf - that should override >> whatever Windows has for you. > > Unless his "8.1" is really 8.1.something-pretty-darn-recent, it's > not going to know about the latest Venezuela DST law change anyway. Yes, good point. Or really, since 8.1 is unsupported, to 8.2.most-recent-available if possible. > This does suggest that we'll need to revisit the win32_tzmap[] list > every so often? Seems so. It's the first time I've heard of a timezone being *added* and not just changed, but obviously it does happen :-( //Magnus
Jorge Campins wrote: > How can I find which version I'm using? All I could see in pgAdmin is 8.1. SELECT version(); > Which value should I use for timezone in postgresql.conf? I tried select > * from pg_timezone_names to get a list of valid time zone names but it > failed with "relation pg_timezone_names does not exist." pg_timezone_names was added in 8.2. (which you really should upgrade to anyway, since 8.1 isn't supported on Windows anymore) To find it in 8.1, check the files in share/timezone in your PostgreSQL installation. //Magnus
How can I find which version I'm using? All I could see in pgAdmin is 8.1. Which value should I use for timezone in postgresql.conf? I tried select * from pg_timezone_names to get a list of valid time zone names but it failed with "relation pg_timezone_names does not exist." Thanks and regards, Jorge ----- Original Message ----- From: "Tom Lane" <tgl@sss.pgh.pa.us> To: "Magnus Hagander" <magnus@hagander.net> Cc: "Jorge Campins" <jrcampins@cantv.net>; <pgsql-bugs@postgresql.org> Sent: Saturday, February 09, 2008 8:25 PM Subject: Re: [BUGS] BUG #3948: date/time functions returning wrong value > Magnus Hagander <magnus@hagander.net> writes: >> Try setting your timezone in postgresql.conf - that should override >> whatever Windows has for you. > > Unless his "8.1" is really 8.1.something-pretty-darn-recent, it's > not going to know about the latest Venezuela DST law change anyway. > > This does suggest that we'll need to revisit the win32_tzmap[] list > every so often? > > regards, tom lane
In 8.2, use pg_timezone_names to find the proper one for you - it'll exist once you've upgraded :-) //Magnus Jorge Campins wrote: > I'll upgrade to 8.2 ASAP. Once in 8.2, which value should I use for > timezone in postgresql.conf? > > Thanks and regards, > > Jorge > > ----- Original Message ----- From: "Magnus Hagander" <magnus@hagander.net> > To: "Jorge Campins" <jrcampins@cantv.net> > Cc: "Tom Lane" <tgl@sss.pgh.pa.us>; <pgsql-bugs@postgresql.org> > Sent: Sunday, February 10, 2008 5:39 AM > Subject: Re: [BUGS] BUG #3948: date/time functions returning wrong value > > >> Jorge Campins wrote: >>> How can I find which version I'm using? All I could see in pgAdmin is >>> 8.1. >> >> SELECT version(); >> >> >>> Which value should I use for timezone in postgresql.conf? I tried select >>> * from pg_timezone_names to get a list of valid time zone names but it >>> failed with "relation pg_timezone_names does not exist." >> >> pg_timezone_names was added in 8.2. (which you really should upgrade to >> anyway, since 8.1 isn't supported on Windows anymore) >> >> To find it in 8.1, check the files in share/timezone in your PostgreSQL >> installation. >> >> //Magnus
I'll upgrade to 8.2 ASAP. Once in 8.2, which value should I use for timezone in postgresql.conf? Thanks and regards, Jorge ----- Original Message ----- From: "Magnus Hagander" <magnus@hagander.net> To: "Jorge Campins" <jrcampins@cantv.net> Cc: "Tom Lane" <tgl@sss.pgh.pa.us>; <pgsql-bugs@postgresql.org> Sent: Sunday, February 10, 2008 5:39 AM Subject: Re: [BUGS] BUG #3948: date/time functions returning wrong value > Jorge Campins wrote: >> How can I find which version I'm using? All I could see in pgAdmin is >> 8.1. > > SELECT version(); > > >> Which value should I use for timezone in postgresql.conf? I tried select >> * from pg_timezone_names to get a list of valid time zone names but it >> failed with "relation pg_timezone_names does not exist." > > pg_timezone_names was added in 8.2. (which you really should upgrade to > anyway, since 8.1 isn't supported on Windows anymore) > > To find it in 8.1, check the files in share/timezone in your PostgreSQL > installation. > > //Magnus
Magnus Hagander wrote: > Tom Lane wrote: >> This does suggest that we'll need to revisit the win32_tzmap[] list >> every so often? > > Seems so. It's the first time I've heard of a timezone being *added* and > not just changed, but obviously it does happen :-( Hmm, was this table manually built? I think I see a mistake. It has two entries for "Mexico Standard Time", one of which (the one at GMT-6) is mapped to America/Mexico_City (which I think would be correct), and the other at America/La_Paz (the one at -7). This latter one I think is mistaken -- firstly because the Windows name is probably something else and not "Mexico Standard Time" (perhaps "Western Mexico Std Time" or something like that?), and secondly because America/La_Paz refers to Bolivia's La Paz and not Mexico's. Mexico's La Paz should probably be mapped to America/Chihuahua or America/Mazatlan. It's hard to tell though -- I am not sure how does Windows define timezones. I have always been annoyed by the fact that Chilean timezone is nowhere near it's database (I think the closest is Bogota, but it's really bogus because it's not even in the same hemisphere). Fortunately this means there's no bogus entry for Chile in this struct ... I think what I conclude from this is that Windows TZ database is so bogus that we should avoid trying to rely on it -- I say if the user does not set "timezone" in postgresql.conf, refuse to start. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Alvaro Herrera <alvherre@commandprompt.com> writes:
> I think what I conclude from this is that Windows TZ database is so
> bogus that we should avoid trying to rely on it -- I say if the user
> does not set "timezone" in postgresql.conf, refuse to start.
Remember we're also relying on the OS for the time of day :-(.
I'm not sure there's any point in trying to be a lot better than
it is about timekeeping. In particular, even if the user has set
the displayed local time correctly, what Windows will tell us the
UTC time is depends entirely on its idea of the timezone offset.
If we have a different idea of the timezone offset, all it will buy
us is complaints from users that our time is wrong.
(Now, this pessimistic view of things might be obsolete if Windows
systems commonly get their UTC time from NTP, as is standard on
Linux and Mac these days. I hadn't heard that Microsoft was up
to speed on that, though --- if they did, I think it'd force them
to be a whole lot more careful keeping their local timezone knowledge
up to date ...)
regards, tom lane
On Sun, Feb 10, 2008 at 09:33:46PM -0500, Tom Lane wrote: > Alvaro Herrera <alvherre@commandprompt.com> writes: > > I think what I conclude from this is that Windows TZ database is so > > bogus that we should avoid trying to rely on it -- I say if the user > > does not set "timezone" in postgresql.conf, refuse to start. > > Remember we're also relying on the OS for the time of day :-(. > I'm not sure there's any point in trying to be a lot better than > it is about timekeeping. In particular, even if the user has set > the displayed local time correctly, what Windows will tell us the > UTC time is depends entirely on its idea of the timezone offset. > If we have a different idea of the timezone offset, all it will buy > us is complaints from users that our time is wrong. > > (Now, this pessimistic view of things might be obsolete if Windows > systems commonly get their UTC time from NTP, as is standard on > Linux and Mac these days. I hadn't heard that Microsoft was up > to speed on that, though --- if they did, I think it'd force them > to be a whole lot more careful keeping their local timezone knowledge > up to date ...) Since Windows 2000, all domain members in a Windows domain member machines will synchronize their time with NTP. It's a requirement of Kerberos that the clocks don't drift. Recommended best practice is to have your domain controller sync to either an external source on the net or to a GPS. Since Windows 2000, there has been an NTP client included in Windows. Since Windows XP, I think (could be 2000), all non-domain machines will ntp-synchronize with servers at microsoft by default. Since Windows NT, it's been possible and recommended to synchronize all members of a domain to the domain controllers with the "net time" tools available. //Magnus
On Sun, Feb 10, 2008 at 11:22:53PM -0300, Alvaro Herrera wrote: > Magnus Hagander wrote: > > Tom Lane wrote: > > >> This does suggest that we'll need to revisit the win32_tzmap[] list > >> every so often? > > > > Seems so. It's the first time I've heard of a timezone being *added* and > > not just changed, but obviously it does happen :-( > > Hmm, was this table manually built? I think I see a mistake. It has Yes. And it's obviously in need of updating. > two entries for "Mexico Standard Time", one of which (the one at GMT-6) > is mapped to America/Mexico_City (which I think would be correct), and > the other at America/La_Paz (the one at -7). This latter one I think is Yes, that's clearly a mistake :-( It'll only ever use the first one though... What's in the database is one called "Mexico Stanadrd Time" and another one called "Mexico Standard Time 2". We're missing the "2" there. > It's hard to tell though -- I am not sure how does Windows define > timezones. I have always been annoyed by the fact that Chilean timezone > is nowhere near it's database (I think the closest is Bogota, but it's > really bogus because it's not even in the same hemisphere). Fortunately > this means there's no bogus entry for Chile in this struct ... My TZ database has an entry for Santiago... AFAIK, that's in Chile? -04? Took me less than 30 seconds to find in the GUI for the Timezone settings. The internal entry name is "Pacific SA Standad Time", which we map to America/Santiago. Is that actually bogus? > I think what I conclude from this is that Windows TZ database is so > bogus that we should avoid trying to rely on it -- I say if the user > does not set "timezone" in postgresql.conf, refuse to start. While there are a lot of bogus things about the Windows TZ database, that's not one of them. (the bogusness mostly deals with them not properly tracking changes in DST rules over time - they are only interesetd in rules that are in force *today*) //Magnus
Dear All, I installed 8.2.6.2 and solved my problem. I set Windows time zone to "Caracas" and PostgreSQL "America/Caracas" and now they both give me the right time. WARNING: not only people in Venezuela but also people in Bolivia is affected by this problem. As you might recall, Windows used to have a single time zone for both countries, namely "Caracas/La Paz". If now you set Windows time zone to "La Paz", PostgreSQL 8.2.6.2 will use "America/Caracas" unless you specify something else in postgresql.conf. In this case, Windows has Bolivia's time and PostgreSQL has Venezuela's time. So people in Bolivia should also specify timezone in postgresql.conf. Thank you all very much for your kind support. Regards, Jorge ----- Original Message ----- From: "Magnus Hagander" <magnus@hagander.net> To: "Alvaro Herrera" <alvherre@commandprompt.com> Cc: "Tom Lane" <tgl@sss.pgh.pa.us>; "Jorge Campins" <jrcampins@cantv.net>; <pgsql-bugs@postgresql.org> Sent: Monday, February 11, 2008 4:05 AM Subject: Re: [BUGS] BUG #3948: date/time functions returning wrong value > On Sun, Feb 10, 2008 at 11:22:53PM -0300, Alvaro Herrera wrote: >> Magnus Hagander wrote: >> > Tom Lane wrote: >> >> >> This does suggest that we'll need to revisit the win32_tzmap[] list >> >> every so often? >> > >> > Seems so. It's the first time I've heard of a timezone being *added* >> > and >> > not just changed, but obviously it does happen :-( >> >> Hmm, was this table manually built? I think I see a mistake. It has > > Yes. And it's obviously in need of updating. > > >> two entries for "Mexico Standard Time", one of which (the one at GMT-6) >> is mapped to America/Mexico_City (which I think would be correct), and >> the other at America/La_Paz (the one at -7). This latter one I think is > > Yes, that's clearly a mistake :-( It'll only ever use the first one > though... > > What's in the database is one called "Mexico Stanadrd Time" and another > one > called "Mexico Standard Time 2". We're missing the "2" there. > > >> It's hard to tell though -- I am not sure how does Windows define >> timezones. I have always been annoyed by the fact that Chilean timezone >> is nowhere near it's database (I think the closest is Bogota, but it's >> really bogus because it's not even in the same hemisphere). Fortunately >> this means there's no bogus entry for Chile in this struct ... > > My TZ database has an entry for Santiago... AFAIK, that's in Chile? -04? > Took me less than 30 seconds to find in the GUI for the Timezone settings. > > The internal entry name is "Pacific SA Standad Time", which we map to > America/Santiago. > > Is that actually bogus? > > >> I think what I conclude from this is that Windows TZ database is so >> bogus that we should avoid trying to rely on it -- I say if the user >> does not set "timezone" in postgresql.conf, refuse to start. > > While there are a lot of bogus things about the Windows TZ database, > that's > not one of them. (the bogusness mostly deals with them not properly > tracking changes in DST rules over time - they are only interesetd in > rules > that are in force *today*) > > //Magnus
Jorge Campins wrote: > Dear All, > > I installed 8.2.6.2 and solved my problem. I set Windows time zone to > "Caracas" and PostgreSQL "America/Caracas" and now they both give me the > right time. > > WARNING: not only people in Venezuela but also people in Bolivia is > affected by this problem. As you might recall, Windows used to have a > single time zone for both countries, namely "Caracas/La Paz". If now you > set Windows time zone to "La Paz", PostgreSQL 8.2.6.2 will use > "America/Caracas" unless you specify something else in postgresql.conf. > In this case, Windows has Bolivia's time and PostgreSQL has Venezuela's > time. So people in Bolivia should also specify timezone in postgresql.conf. Yeah, I've got an update that has some more timezones around here available coming up, I think that's going to help it. But always - the pg database of timezones is a lot more extensive than the Windows one, so it's always a good idea to check it out. //Magnus