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
------------------------------
