From: Achilleus Mantzios <achill@matrix.gatewaynet.com>
To: andrew@supernews.com
Cc: pgsql-sql@postgresql.org
Subject: Re: Timestamp with timezone question.
Date: Mon, 21 Feb 2005 18:44:04 +0200 (EET)
Message-ID: <Pine.LNX.4.44.0502211843001.7436-100000@matrix.gatewaynet.com> (raw)
In-Reply-To: <slrnd1k00q.1ofc.andrew+nonews@trinity.supernews.net>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: achill@matrix.gatewaynet.com, andrew@supernews.com
Subject: Re: Timestamp with timezone question.
In-Reply-To: <Pine.LNX.4.44.0502211843001.7436-100000@matrix.gatewaynet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox