Re: [HACKERS] Problem with copying abstimes

On Thu, 19 Jun 1997, Ronald Baljeu wrote:

> Thomas G. Lockhart wrote:
> >
> > Ronald Baljeu wrote:
> > >
> > > The time 18:41:48 has become 19:41:48. I just upgraded to v6.1 and this
> > > has never happened before.
> > > Platform is Linux 2.0.29. Compiler is gcc 2.7.2.1. C-library: 5.4.33
> > > Any ideas?
> >
> > Yup. There is probably confusion in Postgres with your timezone
> > character strings. I have a similar installation environment, but my
> > US/Pacific timezone (PST8PDT) does not exhibit the behavior you see.
> >

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.

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.

If this is not possible, would it be possible for Postgres to check the
timezone string in the incoming abstime value against the local machine's
timezone string and not make any adjustment if it matches?

I think the first method would be more general and preferable. Could it be
an option, keeping the current method for compatibility?

Thanks.

+----------------------+---+
| Ross Johnson         |   | E-Mail: rpj@ise.canberra.edu.au
| Info Sciences and Eng|___|
| University of Canberra   | FAX:    +61 6 2015227
| PO Box 1                 |
| Belconnen  ACT    2616   | WWW:    http://willow.canberra.edu.au/~rpj/
| AUSTRALIA                |
+--------------------------+

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

End of hackers-digest V1 #392
*****************************