Date: Mon, 21 Feb 2005 18:44:04 +0200 (EET)
From: Achilleus Mantzios <achill@matrix.gatewaynet.com>
To: andrew@supernews.com
Cc: pgsql-sql@postgresql.org
Subject: Re: Timestamp with timezone question.
In-Reply-To: <slrnd1k00q.1ofc.andrew+nonews@trinity.supernews.net>
Message-ID: <Pine.LNX.4.44.0502211843001.7436-100000@matrix.gatewaynet.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=ISO-8859-7
Content-Transfer-Encoding: 8BIT

O Andrew - Supernews έγραψε στις Feb 21, 2005 :

> On 2005-02-21, Achilleus Mantzios <achill@matrix.gatewaynet.com> wrote:
> > Consider a schema designed to store internet mail.
> >
> > Since pgsql always converts a timestamptz to UTC, we have lost
> > the information of the Sender's local timezone.
> >
> > Should i go with a separete date and timetz ?
> 
> No. Consider instead storing a timestamptz with the actual time of the
> mail, and a separate field with an interval representing the zone offset.
> Then you can use AT TIME ZONE to recover the sender's local time.
> 
> e.g. (this table has columns serial, timestamptz, interval)
> 
> insert into dtz values (DEFAULT,
>   '2005-03-21 07:05:00 -0800',
>   '2005-03-21 07:05:00 -0800'::timestamp
>     - '2005-03-21 07:05:00 -0800'::timestamptz at time zone 'UTC'
> );
> 
> (the timestamp - timestamptz thing is just a reasonably reliable way of
> getting the timezone offset without complicated parsing.)
> 
> select * from dtz;
>  id |           t            |     z     
> ----+------------------------+-----------
>   1 | 2005-03-21 15:05:00+00 | -08:00:00
> (1 row)
> 
> select *, t at time zone z as ot from dtz;
>  id |           t            |     z     |         ot          
> ----+------------------------+-----------+---------------------
>   1 | 2005-03-21 15:05:00+00 | -08:00:00 | 2005-03-21 07:05:00
> (1 row)

Cool thanx.

I ended up displaying the actual date header field of the SMTP message
(just like the yahoo guys do).

> 
> 

-- 
-Achilleus