Re: timestamp with time zone - Mailing list pgsql-general

From Magnus Hagander
Subject Re: timestamp with time zone
Date
Msg-id 200712130745190000@2192011050
Whole thread Raw
In response to timestamp with time zone  (Tatsuo Ishii <ishii@postgresql.org>)
Responses Re: timestamp with time zone
List pgsql-general
> > 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


pgsql-general by date:

Previous
From: Tom Lane
Date:
Subject: Re: Slow PITR restore
Next
From: Kris Jurka
Date:
Subject: Re: RETURNING clause: how to specifiy column indexes?