Thread: 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
On Dec 12, 2007 11:10 PM, Tatsuo Ishii <ishii@sraoss.co.jp> wrote: > 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. Yep, that's correct. Basically, when you store a timestamp with timezone, postgresql converts it to UTC and stores that. when you get it back, it converts it to the timezone of the client currently connected. If you want to store the timezone you'll have to create another field to hold that.