pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedTimestamp with timezone question.
5+ messages / 3 participants
[nested] [flat]
* Timestamp with timezone question.
@ 2005-02-21 14:16 Achilleus Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 2 replies; 5+ messages in thread
From: Achilleus Mantzios @ 2005-02-21 14:16 UTC (permalink / raw)
To: pgsql-sql
AFAIK, the input for a timestamptz is converted and stored as UTC.
And outputing a timezonetz value converts the internally stored UTC
value to the current locale's timezone.
So there is not a way to actually store the original TZ itself,
whereas the timetz type clearly does that.
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 ?
--
-Achilleus
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Timestamp with timezone question.
@ 2005-02-21 15:36 Andrew - Supernews <andrew+nonews@supernews.com>
parent: Achilleus Mantzios <achill@matrix.gatewaynet.com>
1 sibling, 1 reply; 5+ messages in thread
From: Andrew - Supernews @ 2005-02-21 15:36 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Timestamp with timezone question.
@ 2005-02-21 16:44 Achilleus Mantzios <achill@matrix.gatewaynet.com>
parent: Andrew - Supernews <andrew+nonews@supernews.com>
0 siblings, 0 replies; 5+ messages in thread
From: Achilleus Mantzios @ 2005-02-21 16:44 UTC (permalink / raw)
To: andrew@supernews.com; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Timestamp with timezone question.
@ 2005-02-22 06:59 Bruno Wolff III <bruno@wolff.to>
parent: Achilleus Mantzios <achill@matrix.gatewaynet.com>
1 sibling, 1 reply; 5+ messages in thread
From: Bruno Wolff III @ 2005-02-22 06:59 UTC (permalink / raw)
To: Achilleus Mantzios <achill@matrix.gatewaynet.com>; +Cc: pgsql-sql
On Mon, Feb 21, 2005 at 16:16:04 +0200,
Achilleus Mantzios <achill@matrix.gatewaynet.com> wrote:
>
> 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 ?
Someone else gave you a recommended solution.
However there was a discussion on the order of 6 months ago about changing
the timestamptz type to keep this information. Nothing will be changing
in the near future, but you may (or may not) find the discussion useful.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Timestamp with timezone question.
@ 2005-02-22 08:24 Achilleus Mantzios <achill@matrix.gatewaynet.com>
parent: Bruno Wolff III <bruno@wolff.to>
0 siblings, 0 replies; 5+ messages in thread
From: Achilleus Mantzios @ 2005-02-22 08:24 UTC (permalink / raw)
To: Bruno Wolff III <bruno@wolff.to>; +Cc: pgsql-sql
O Bruno Wolff III Ýãñáøå óôéò Feb 22, 2005 :
> On Mon, Feb 21, 2005 at 16:16:04 +0200,
> Achilleus Mantzios <achill@matrix.gatewaynet.com> wrote:
> >
> > 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 ?
>
> Someone else gave you a recommended solution.
>
> However there was a discussion on the order of 6 months ago about changing
> the timestamptz type to keep this information. Nothing will be changing
> in the near future, but you may (or may not) find the discussion useful.
Thanx,
i'll check it out sometime.
Apart from that, having a new (version of a) type (timestamptz)
just to provide a convinience function isnt too useful.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
>
--
-Achilleus
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2005-02-22 08:24 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-02-21 14:16 Timestamp with timezone question. Achilleus Mantzios <achill@matrix.gatewaynet.com>
2005-02-21 15:36 ` Andrew - Supernews <andrew+nonews@supernews.com>
2005-02-21 16:44 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
2005-02-22 06:59 ` Bruno Wolff III <bruno@wolff.to>
2005-02-22 08:24 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
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