Re: Timestamp with timezone question.

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