Re: [HACKERS] Problem with copying abstimes

Ross Johnson wrote:
> On this topic, I had a similar problem a while back copying back in a
> dumped database. 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?!

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

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.

Cheers,
Ronald

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