Thread: Server Time Setting
Tom Lane suggested that perhaps this mailing list would be a better place
to ask the following question ... since we have an application that is very
dependent on the accuracy of timestamps, this is a big issue to us.
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Monday, September 12, 2005 6:42 PM
To: Lane Van Ingen
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Server Time Setting
"Lane Van Ingen" <lvaningen@esncc.com> writes:
>> I am baffled as to why some of the PostgreSQL functions (like now() ) are
>> returning a time which is exactly an hour later than the server's actual
>> system time.
> I'd say you've got TimeZone set to something different than you think
> you do.
> regards, tom lane
Before I asked this question yesterday, I had already checked SHOW ALL and
found only two PostgreSQL config parameters that seemed to apply, and they
appeared to be set properly:
australian_timezones = no
TimeZone = US/Eastern
We are running 8.0.1, Windows 2003.
Since last message, checked 3 other servers, and found that I have the same
problem on two out of three:
Server #1: select now() agreed with Windows time on this one
Server #2: select now() was exactly one hour greater than Windows time
where config parms were set the same as previous paragraph
Server #3: select now() was exactly one hour greater than Windows time,
but config parm was set wrong: TimeZone = Europe/Dublin (don't
know how that happened; makes you wonder if TimeZone has
anything to do with this at all!)
Windows 2003 time was set correctly in all four cases.
Summary:
- one server displays time correctly
- two servers APPEAR to be set correctly in Windows, but time displayed
from now() is one hour greater than Windows time
- one server is set incorrectly (PostgreSQL TimeZone parameter was set to
Dublin/Europe); but TimeZone did not appear to have any effect on the
time being displayed, since now() is displaying time as one hour greater
than Windows time, and Windows time appears to be set correctly
Being as one server is 'doing it right', there must be some difference in
settings, but so far I have not been able to determine what is different.
Windows appeared to be correct in all cases, but PostgreSQL is not.
---------------------------(end of broadcast)---------------------------
When you say time reads correctly for Windows 2003, I assume you also mean
not just the time but that the Time Zone tab of windows 2003 Adjust time
reads "Eastern Time (US & Canada)"?
-----Original Message-----
From: Lane Van Ingen [mailto:lvaningen@esncc.com]
Sent: Thursday, September 15, 2005 10:13 AM
To: pgsql-hackers-win32@postgresql.org
Subject: [pgsql-hackers-win32] Server Time Setting
Tom Lane suggested that perhaps this mailing list would be a better place to
ask the following question ... since we have an application that is very
dependent on the accuracy of timestamps, this is a big issue to us.
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Monday, September 12, 2005 6:42 PM
To: Lane Van Ingen
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Server Time Setting
"Lane Van Ingen" <lvaningen@esncc.com> writes:
>> I am baffled as to why some of the PostgreSQL functions (like now() )
>> are returning a time which is exactly an hour later than the server's
>> actual system time.
> I'd say you've got TimeZone set to something different than you think
>you do.
> regards, tom lane
Before I asked this question yesterday, I had already checked SHOW ALL and
found only two PostgreSQL config parameters that seemed to apply, and they
appeared to be set properly:
australian_timezones = no
TimeZone = US/Eastern
We are running 8.0.1, Windows 2003.
Since last message, checked 3 other servers, and found that I have the same
problem on two out of three:
Server #1: select now() agreed with Windows time on this one
Server #2: select now() was exactly one hour greater than Windows time
where config parms were set the same as previous paragraph
Server #3: select now() was exactly one hour greater than Windows time,
but config parm was set wrong: TimeZone = Europe/Dublin (don't
know how that happened; makes you wonder if TimeZone has
anything to do with this at all!)
Windows 2003 time was set correctly in all four cases.
Summary:
- one server displays time correctly
- two servers APPEAR to be set correctly in Windows, but time displayed
from now() is one hour greater than Windows time
- one server is set incorrectly (PostgreSQL TimeZone parameter was set to
Dublin/Europe); but TimeZone did not appear to have any effect on the
time being displayed, since now() is displaying time as one hour greater
than Windows time, and Windows time appears to be set correctly
Being as one server is 'doing it right', there must be some difference in
settings, but so far I have not been able to determine what is different.
Windows appeared to be correct in all cases, but PostgreSQL is not.
---------------------------(end of broadcast)---------------------------
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
> Being as one server is 'doing it right', there must be some difference in > settings, but so far I have not been able to determine what is different. > Windows appeared to be correct in all cases, but PostgreSQL is not. Have you ruled out automatic adjustment for daylight savings time? Merlin
Yes, it reads (GMT-05:00) Eastern Time (US & Canada).
Looks like I may have just solved my own problem: I noticed that in Date and
Time Properties of the Windows clock, on the Time Zone tab, there is a
checkbox
called "Automatically adjust clock for daylight savings time changes".
If that box is unchecked, PostgreSQL must try to compensate, because on
those
platforms that are unchecked is where I am having the problem of now()
returning
a date that is one hour later. It now works correctly.
Thanks for causing me to check this out; it looks like my problem is solved!
:-)
-----Original Message-----
From: Obe, Regina DND\MIS [mailto:robe.dnd@cityofboston.gov]
Sent: Thursday, September 15, 2005 9:44 AM
To: 'Lane Van Ingen'; 'pgsql-hackers-win32@postgresql.org'
Subject: RE: [pgsql-hackers-win32] Server Time Setting
When you say time reads correctly for Windows 2003, I assume you also mean
not just the time but that the Time Zone tab of windows 2003 Adjust time
reads "Eastern Time (US & Canada)"?
-----Original Message-----
From: Lane Van Ingen [mailto:lvaningen@esncc.com]
Sent: Thursday, September 15, 2005 10:13 AM
To: pgsql-hackers-win32@postgresql.org
Subject: [pgsql-hackers-win32] Server Time Setting
Tom Lane suggested that perhaps this mailing list would be a better place to
ask the following question ... since we have an application that is very
dependent on the accuracy of timestamps, this is a big issue to us.
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Monday, September 12, 2005 6:42 PM
To: Lane Van Ingen
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Server Time Setting
"Lane Van Ingen" <lvaningen@esncc.com> writes:
>> I am baffled as to why some of the PostgreSQL functions (like now() )
>> are returning a time which is exactly an hour later than the server's
>> actual system time.
> I'd say you've got TimeZone set to something different than you think
>you do.
> regards, tom lane
Before I asked this question yesterday, I had already checked SHOW ALL and
found only two PostgreSQL config parameters that seemed to apply, and they
appeared to be set properly:
australian_timezones = no
TimeZone = US/Eastern
We are running 8.0.1, Windows 2003.
Since last message, checked 3 other servers, and found that I have the same
problem on two out of three:
Server #1: select now() agreed with Windows time on this one
Server #2: select now() was exactly one hour greater than Windows time
where config parms were set the same as previous paragraph
Server #3: select now() was exactly one hour greater than Windows time,
but config parm was set wrong: TimeZone = Europe/Dublin (don't
know how that happened; makes you wonder if TimeZone has
anything to do with this at all!)
Windows 2003 time was set correctly in all four cases.
Summary:
- one server displays time correctly
- two servers APPEAR to be set correctly in Windows, but time displayed
from now() is one hour greater than Windows time
- one server is set incorrectly (PostgreSQL TimeZone parameter was set to
Dublin/Europe); but TimeZone did not appear to have any effect on the
time being displayed, since now() is displaying time as one hour greater
than Windows time, and Windows time appears to be set correctly
Being as one server is 'doing it right', there must be some difference in
settings, but so far I have not been able to determine what is different.
Windows appeared to be correct in all cases, but PostgreSQL is not.
---------------------------(end of broadcast)---------------------------
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
"Lane Van Ingen" <lvaningen@ESNCC.com> writes:
> Looks like I may have just solved my own problem: I noticed that in Date and
> Time Properties of the Windows clock, on the Time Zone tab, there is a
> checkbox
> called "Automatically adjust clock for daylight savings time changes".
> If that box is unchecked, PostgreSQL must try to compensate, because on
> those
> platforms that are unchecked is where I am having the problem of now()
> returning
> a date that is one hour later. It now works correctly.
Hmm. I think the way that the code in pgtz.c is set up, it just assumes
that either "Eastern Standard Time" or "Eastern Daylight Time" should
map to our US/Eastern timezone (which is a DST-aware zone). Running
your system in non-DST-aware mode is what's confusing it --- the offset
to GMT is an hour different than it "should be" at this time of year.
Should pgtz.c try to detect this situation and handle it by mapping to a
non-DST-aware internal timezone?
regards, tom lane
Tom Lane wrote: >Hmm. I think the way that the code in pgtz.c is set up, it just assumes >that either "Eastern Standard Time" or "Eastern Daylight Time" should >map to our US/Eastern timezone (which is a DST-aware zone). Running >your system in non-DST-aware mode is what's confusing it --- the offset >to GMT is an hour different than it "should be" at this time of year. > >Should pgtz.c try to detect this situation and handle it by mapping to a >non-DST-aware internal timezone? > > > > I think this is a simple case of misconfiguration and I suspect fiddling with it would just open up the possibility of more errors. If you say you are in a DST timezone and you don't check the DST checkbox, and you set the time to summer time then you're asking for trouble. I would not be at all surprised if the machine's idea of UTC was an hour out (Windows boxes keep the RTC on local time, not UTC, which is just horrid). cheers andrew
"Lane Van Ingen" <lvaningen@ESNCC.com> writes:
> Looks like I may have just solved my own problem: I noticed that in Date
and
> Time Properties of the Windows clock, on the Time Zone tab, there is a
> checkbox
> called "Automatically adjust clock for daylight savings time changes".
> If that box is unchecked, PostgreSQL must try to compensate, because on
> those
> platforms that are unchecked is where I am having the problem of now()
> returning
> a date that is one hour later. It now works correctly.
Hmm. I think the way that the code in pgtz.c is set up, it just assumes
that either "Eastern Standard Time" or "Eastern Daylight Time" should
map to our US/Eastern timezone (which is a DST-aware zone). Running
your system in non-DST-aware mode is what's confusing it --- the offset
to GMT is an hour different than it "should be" at this time of year.
Should pgtz.c try to detect this situation and handle it by mapping to a
non-DST-aware internal timezone?
regards, tom lane
I think this is a simple case of misconfiguration and I suspect fiddling
with it would just open up the possibility of more errors. If you say
you are in a DST timezone and you don't check the DST checkbox, and you
set the time to summer time then you're asking for trouble. I would not
be at all surprised if the machine's idea of UTC was an hour out
(Windows boxes keep the RTC on local time, not UTC, which is just horrid).
cheers
andrew (dunstan)
If I am following this correctly, I may have solved the problem for myself
in
the Eastern time zone, but this application gets deployed around the world,
anywhere at sea that a vessel can go. While I think the vessel sets its time
based on its home port and keeps it that way through voyage(s), I am won-
dering if handling of time is still going to give me problems outside of
time
zones where DST is being observed.
Lane Van Ingen
Andrew Dunstan <andrew@dunslane.net> writes:
> Tom Lane wrote:
>> Should pgtz.c try to detect this situation and handle it by mapping to a
>> non-DST-aware internal timezone?
> I think this is a simple case of misconfiguration and I suspect fiddling
> with it would just open up the possibility of more errors.
Maybe. The other thing that's worrisome here is the likelihood that
people would fiddle with the setting after starting Postgres; which
is a scenario we really can't do much about, but AFAICS it would have
the same effect that now() would deviate from what the system clock
is showing.
I'm not sure it's worth the trouble to try to handle the "box not
checked" case fully --- as you say, anyone who's handling DST by
manually adjusting the system clock twice a year is going to lose
anyway. But if we can detect that setting easily, maybe we could just
insist that it match the behavior we're expecting (ie, fail to find a
matching timezone and complain about it)? It seems to me better that
there be an obvious failure immediately than that the system appear to
work only to start giving wrong answers after the next time change.
regards, tom lane
Lane Van Ingen wrote: >If I am following this correctly, I may have solved the problem for myself >in >the Eastern time zone, but this application gets deployed around the world, >anywhere at sea that a vessel can go. While I think the vessel sets its time >based on its home port and keeps it that way through voyage(s), I am won- >dering if handling of time is still going to give me problems outside of >time >zones where DST is being observed. > > > > If they set the machine to the home port's time and leave it there that should be no problem, as long as they select DST adjustment if it's a zone that uses DST. In fact I suspect they can set it anyway, regardless of TZ, because if the zone doesn't have DST nothing should happen. Test it and see. The trouble with any sort of detection/correction would be that if the DST flag is off we have no idea whether the user has left the time on the zone's base and so it shows an hour's difference from actual local time, or if they have set it to the DST time manually and thus more or less lied about the value of UTC. I've seen both happen, many times. cheers andrew