Postgres Pro
Downloads
Home   >   mailing lists

Thread: timestamp with time zone

timestamp with time zone

From
Tatsuo Ishii
Date:
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

Re: timestamp with time zone

From
"Joshua D. Drake"
Date:
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

Re: timestamp with time zone

From
Tatsuo Ishii
Date:
> 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

Re: timestamp with time zone

From
"Magnus Hagander"
Date:
> > 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
asargument) 

/Magnus

Re: timestamp with time zone

From
Tatsuo Ishii
Date:
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
asargument) 

Or use date + time with time zone.
--
Tatsuo Ishii
SRA OSS, Inc. Japan

Re: timestamp with time zone

From
ptjm@interlog.com (Patrick TJ McPhee)
Date:
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