From: Andrew - Supernews <andrew+nonews@supernews.com>
To: pgsql-sql@postgresql.org
Subject: Re: Timestamp with timezone question.
Date: Mon, 21 Feb 2005 15:36:58 -0000
Message-ID: <slrnd1k00q.1ofc.andrew+nonews@trinity.supernews.net> (raw)
References: <Pine.LNX.4.44.0502211557140.7123-100000@matrix.gatewaynet.com>
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)
--
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services
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: andrew+nonews@supernews.com
Subject: Re: Timestamp with timezone question.
In-Reply-To: <slrnd1k00q.1ofc.andrew+nonews@trinity.supernews.net>
* 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