Re: automatic time zone conversion

On Wed, Jun 12, 2002 at 04:41:53PM +1000, Ken Williams wrote:
> Hi,
>
> I'm trying to import some data into a table with a column
> defined as "timestamp not null". When I defined the table,
> postgres seemed to automatically convert the column to
> "timestamp with time zone not null", and I can't figure out how
> to get rid of the time zone information.

I guess you could alter the schema so the type was "timestamp without time
zone" (IIRC).

> Question two is about how to import data so that time zones are
> correct. I'm importing a bunch of dates that occurred in
> Sydney, Australia. The dates are spread throughout the year, so
> some will be in standard time and some in daylight time. What's
> the proper way to let postgres take care of this detail,
> figuring out which ones are standard time and which are
> daylight? If I don't do this, it seems that all my summer dates
> will be 1 hour off, or I'll have to manually figure out which
> dates are in which section of the year.

Hmm, postgresql knows about daylight savings if your c library knows about
it. I'm not exactly sure how it works but you should investigate the PGTZ
environment variables. This is what happens on my 7.2.1 system:

select '02/06/2002 12:00:00 AEST'::timestamp;
timestamptz
------------------------
2002-06-02 12:00:00+10
(1 row)

select '02/03/2002 12:00:00 AEST'::timestamp;
timestamptz
------------------------
2002-03-02 13:00:00+11
(1 row)

which seems wrong to me...

HTH,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> There are 10 kinds of people in the world, those that can do binary
> arithmetic and those that can't.