X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id C2C298BB024 for ; Mon, 21 Feb 2005 15:38:08 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 06921-10 for ; Mon, 21 Feb 2005 15:38:01 +0000 (GMT) Received: from floppy.pyrenet.fr (floppy.pyrenet.fr [194.250.190.2]) by svr1.postgresql.org (Postfix) with ESMTP id 39D058BAFCF for ; Mon, 21 Feb 2005 15:38:04 +0000 (GMT) Received: by floppy.pyrenet.fr (Postfix, from userid 106) id 0433F31FE9; Mon, 21 Feb 2005 16:38:04 +0100 (MET) From: Andrew - Supernews X-Newsgroups: pgsql.sql Subject: Re: Timestamp with timezone question. Date: Mon, 21 Feb 2005 15:36:58 -0000 Organization: http://www.supernews.com - all your nntp are belong to us Message-ID: References: Reply-To: andrew@supernews.com User-Agent: slrn/0.9.8.0 (FreeBSD) X-Complaints-To: abuse@supernews.com Lines: 38 To: pgsql-sql@postgresql.org X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.006 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200502/224 X-Sequence-Number: 20335 On 2005-02-21, Achilleus Mantzios 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