Re: [HACKERS] Problem with copying abstimes - Mailing list pgsql-hackers

Woh, I am sorry to jump in here, but we have to go by whatever Unix
gives us as a timezone.  If Unix can't get it right, I don't think
postgreSQL should be messing with it.

Or am I missing something here.  Are there timezones hard-coded into
PostgreSQL?


>
> Ronald Baljeu wrote:
> > > My timezone, as set by the standard zoneinfo file for my
> > > area, is EST. Even though PostgreSQL knowns this as Eastern USA, it is
> > > also typically the string used in Eastern Australia. Consequently, a copy
> > > out generates EST strings and then the copy in generates an unwanted 15
> > > hour correction.
> >
> > Argh! I knew time is a difficult and messy problem, but this is
> > *REALLY* bad. Does this mean we shouldn't rely on the timezone strings?!
>
> Only if you live in Australia (just joking...)
> This seems to be a problem! Let's figure out a way to get the behavior
> you want, even if it is as kludgy as having a USE_AUSTRALIAN_TIME
> compile-time parameter.
>
> > > Assuming that there is no official standard for timezone strings (see the
> > > thousands of netnews articles on this topic over the past decade), is
> > > there any way that a future release of Postgres could use a different
> > > mechanism for this feature.
> > >
> > > I'm sure that a lot of thought has already gone into this and so I may be
> > > totally wrong to suggest it but, would it be possible for the copy out/in
> > > code to be changed to convert to/from UTC (or GMT) time? (That is,
> > > dumped abstime values are always in UTC.) I think all machines understand
> > > the difference between UTC/GMT and local time.
> >
> > I think this is a good idea. In that case we finally got rid of those
> > #@$%*&!% timezones. Would this solve all abstime related problems,
> > (including Daylight So-called-savings Time, time leaps and worm holes) ?
> >
> > I took a quick look at the source (src/backend/utils/adt/nabstime.c). It
> > really boggles my mind.
>
> Well, thank you! Though the really fun code is in dt.c...
>
> > I think for the mean time (well, not quite: GMT+1 ;-) I'll just add
> > a quick hack in my application in order to ignore 1 hour differences
> > between 2 abstimes when comparing them.
>
> I'll think about a conditional compilation for Australia timezones
> (which would substitute the EST interpretation). Are there any other
> 3-character strings which . In the long run, perhaps we can support
> entire new timezone rules as a run-time option, for example, but it may
> be that for performance reasons a hardcoded table is best. In the
> meantime, for dump/reload you could run the backend in GMT (I run all of
> my machines in GMT) and this would eliminate the ambiguity in timezone.
>
> btw, you probably already know that there _are_ several timezone strings
> in Postgres to handle Australia; AEST, AESST, ACST, ACSST, AWST, AWSST,
> CADT, CAST, EAST, LIGT, SADT, SAST, WADT, WAST, WDT, WST are all in
> there. In fact, seems that Australia is overrepresented with time zone
> support :)
>
>             - Tom
>
>


- --
Bruce Momjian
maillist@candle.pha.pa.us

------------------------------