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