Обсуждение: Time Zone Error
Hi, We currently running Windows 2003 issues with time zone set to (GMT-05:00) Eastern Time (US & Canada). Problem that we currently have with Postgresql 8.1.5 is that time zone is being reported with a incorrect off set –4 not –5 and this is causing incorrect time to be set. Below is example out put Windows Time 2007-05-17 00:55:32.327-05 Postgresql Time 2007-05-17 01:55:32.327-04
Jamie Deppeler wrote: > Hi, > > We currently running Windows 2003 issues with time zone set to > (GMT-05:00) Eastern Time (US & Canada). Problem that we currently have > with Postgresql 8.1.5 is that time zone is being reported with a > incorrect off set –4 not –5 and this is causing incorrect time to be set. > > Below is example out put > > Windows Time > 2007-05-17 00:55:32.327-05 > > Postgresql Time > 2007-05-17 01:55:32.327-04 1. These are the same absolute times. 2. Do you have some sort of daylight-saving flag set somewhere? -- Richard Huxton Archonet Ltd
Jamie Deppeler wrote: > > Richard Huxton wrote: >> Jamie Deppeler wrote: >>> Hi, >>> >>> We currently running Windows 2003 issues with time zone set to >>> (GMT-05:00) Eastern Time (US & Canada). Problem that we currently >>> have with Postgresql 8.1.5 is that time zone is being reported with a >>> incorrect off set –4 not –5 and this is causing incorrect time to be >>> set. >>> >>> Below is example out put >>> >>> Windows Time >>> 2007-05-17 00:55:32.327-05 >>> >>> Postgresql Time >>> 2007-05-17 01:55:32.327-04 >> >> 1. These are the same absolute times. > Yes >> 2. Do you have some sort of daylight-saving flag set somewhere? > No it was the first thing i checked Hang on - did the US not alter the daylight-saving date recently? I'll bet that's the issue. http://www.postgresqlforums.com/wiki/2007_Daylight_Savings_Time -- Richard Huxton Archonet Ltd
Richard Huxton wrote: > Jamie Deppeler wrote: >> Hi, >> >> We currently running Windows 2003 issues with time zone set to >> (GMT-05:00) Eastern Time (US & Canada). Problem that we currently >> have with Postgresql 8.1.5 is that time zone is being reported with a >> incorrect off set –4 not –5 and this is causing incorrect time to be >> set. >> >> Below is example out put >> >> Windows Time >> 2007-05-17 00:55:32.327-05 >> >> Postgresql Time >> 2007-05-17 01:55:32.327-04 > > 1. These are the same absolute times. Yes > 2. Do you have some sort of daylight-saving flag set somewhere? No it was the first thing i checked -- *Jamie Deppeler * Programmer *once:technologies pty ltd * 46 Roseneath Street North Geelong Victoria 3215 Australia Ph: +61 3 5278 6699 Fax: +61 3 5278 6166 Email: jamie@doitonce.net.au Web: http://www.oncefabrik.com <http://www.oncefabrik.com/>
Jamie Deppeler <jamie@doitonce.net.au> writes:
> We currently running Windows 2003 issues with time zone set to
> (GMT-05:00) Eastern Time (US & Canada). Problem that we currently have
> with Postgresql 8.1.5 is that time zone is being reported with a
> incorrect off set �4 not �5 and this is causing incorrect time to be set.
> Windows Time
> 2007-05-17 00:55:32.327-05
> Postgresql Time
> 2007-05-17 01:55:32.327-04
At this time of year your GMT offset *should* be -4, no? So I'd venture
that Postgres is right and Windows is wrong --- only you've got the
hardware clock set to make Windows' incorrect output look right. I have
no idea whether Windows 2003 is smart enough to do automatic daylight
savings transitions or not; if it is, perhaps you've turned off that
feature?
If you really want to run the machine in standard time all year, you can
do it, but you'll have to explicitly set the Postgres timezone to that.
I think "timezone = 'EST5'" in postgresql.conf will do the trick.
regards, tom lane
Richard Huxton <dev@archonet.com> writes:
> Hang on - did the US not alter the daylight-saving date recently? I'll
> bet that's the issue.
We're well past both the old and new change dates though, so for any
combination of OS and Postgres knowing/not knowing about the law change,
they ought to be back in sync by now.
[ thinks... ] It seems entirely plausible that Windows 2003 doesn't
know about the law change, especially if it's not been updated lately.
Maybe the OP turned off the automatic DST switchover at the OS level
upon realizing it was wrong? I don't think PG's code for translating
the Windows setting into a PG setting is able to detect that DST
switchover has been disabled.
regards, tom lane
Tom Lane wrote: > Richard Huxton <dev@archonet.com> writes: >> Hang on - did the US not alter the daylight-saving date recently? I'll >> bet that's the issue. > > We're well past both the old and new change dates though, so for any > combination of OS and Postgres knowing/not knowing about the law change, > they ought to be back in sync by now. > > [ thinks... ] It seems entirely plausible that Windows 2003 doesn't > know about the law change, especially if it's not been updated lately. Just for the record in the archives - Windows 2003 *does* require an update to deal with the new timezones. It's pushed out automatically using Windows Update if you have it enabled. //Magnus