Re: Timestamp with timezone question.

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

> On 2005-02-21, Achilleus Mantzios <achill(at)matrix(dot)gatewaynet(dot)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