timestamp with time zone
Hi,
Can someone enlighten me?
I know that I can insert date/time data along with time zone info into
the timestamp with time zone data type. My question is, can I extract
the *original* time zone info afterward? I seems impossible.
May be I should use date + time with time zone to preserve the time
zone info?
It seems the time with time zone data type can do this.
test=# create table t2(t time with time zone);
CREATE TABLE
test=#
test=# insert into t2 values('12:34:56 +0400');
INSERT 0 1
test=# select * from t2;
t
-------------
12:34:56+04
(1 row)
test=# select t at time zone 'jst' from t2;
timezone
-------------
17:34:56+09
(1 row)
test=# select t::time from t2;
t
----------
12:34:56
(1 row)
--
Tatsuo Ishii
SRA OSS, Inc. Japan
Tatsuo Ishii wrote:
Hi,
test=# select t at time zone 'jst' from t2;
timezone
-------------
17:34:56+09
(1 row)test=# select t::time from t2;
t
----------
12:34:56
(1 row)
Hello,
timezone_test=# select cast(t as time with time zone) from t2;
t
-------------
12:34:56+04
(1 row)
?
Sincerely,
Joshua D. Drake
Tatsuo Ishii wrote:
Hi,
test=# select t at time zone 'jst' from t2;
timezone
-------------
17:34:56+09
(1 row)test=# select t::time from t2;
t
----------
12:34:56
(1 row)Hello,
timezone_test=# select cast(t as time with time zone) from t2;
t
-------------
12:34:56+04
(1 row)?
I know that I can it with time with time zone. What I cannot do with
is, timestamp with time zone.
test=# \d t1
Table "public.t1"
Column | Type | Modifiers
--------+--------------------------+-----------
t | timestamp with time zone |
test=# insert into t1 values('2007-12-13 12:34:56 +0400');
INSERT 0 1
test=# select * from t1;
t
------------------------
2007-12-13 17:34:56+09
(1 row)
Can I get "+04" without knowing that I inserted the data using "+0400"
time zone?
--
Tatsuo Ishii
SRA OSS, Inc. Japan
Tatsuo Ishii wrote:
Hi,
test=# select t at time zone 'jst' from t2;
timezone
-------------
17:34:56+09
(1 row)test=# select t::time from t2;
t
----------
12:34:56
(1 row)Hello,
timezone_test=# select cast(t as time with time zone) from t2;
t
-------------
12:34:56+04
(1 row)?
I know that I can it with time with time zone. What I cannot do with
is, timestamp with time zone.test=# \d t1
Table "public.t1"
Column | Type | Modifiers
--------+--------------------------+-----------
t | timestamp with time zone |test=# insert into t1 values('2007-12-13 12:34:56 +0400');
INSERT 0 1
test=# select * from t1;
t
------------------------
2007-12-13 17:34:56+09
(1 row)Can I get "+04" without knowing that I inserted the data using "+0400"
time zone?
No. The closest you can get is to store the tz in a different column and use AT TIMEZONE (which accepts a column name as argument)
/Magnus
Import Notes
Resolved by subject fallback
From: "Magnus Hagander" <magnus@hagander.net>
Subject: Re: [GENERAL] timestamp with time zone
Date: Thu, 13 Dec 2007 07:45:18 +0100
Message-ID: <200712130745190000@2192011050>
Tatsuo Ishii wrote:
Hi,
test=# select t at time zone 'jst' from t2;
timezone
-------------
17:34:56+09
(1 row)test=# select t::time from t2;
t
----------
12:34:56
(1 row)Hello,
timezone_test=# select cast(t as time with time zone) from t2;
t
-------------
12:34:56+04
(1 row)?
I know that I can it with time with time zone. What I cannot do with
is, timestamp with time zone.test=# \d t1
Table "public.t1"
Column | Type | Modifiers
--------+--------------------------+-----------
t | timestamp with time zone |test=# insert into t1 values('2007-12-13 12:34:56 +0400');
INSERT 0 1
test=# select * from t1;
t
------------------------
2007-12-13 17:34:56+09
(1 row)Can I get "+04" without knowing that I inserted the data using "+0400"
time zone?No. The closest you can get is to store the tz in a different column and use AT TIMEZONE (which accepts a column name as argument)
Or use date + time with time zone.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
In article <20071214.001009.67102034.t-ishii@sraoss.co.jp>,
Tatsuo Ishii <ishii@postgresql.org> wrote:
% From: "Magnus Hagander" <magnus@hagander.net>
[...]
% > > Can I get "+04" without knowing that I inserted the data using "+0400"
% > > time zone?
% > No. The closest you can get is to store the tz in a different column
% and use AT TIMEZONE (which accepts a column name as argument)
% Or use date + time with time zone.
This could cause problems if you want to insert a timestamp for a date
with different DST settings. It also seems a lot more cumbersome to do
it this way.
--
Patrick TJ McPhee
North York Canada
ptjm@interlog.com