On Sun, Apr 24, 2005 at 10:49:13AM -0600, Michael Fuhr wrote:
> > > As far as I know, it's not possible to get output like the following
> > > from the same query if the data type is timestamp with time zone:
> > >
> > > 2005-04-21 15:00:00-07
> > > 2005-04-21 22:00:00+00
> >
> > Doesn't "at time zone" do what you need ?
>
> Not as far as I can tell, because if the result is timestamp with
> time zone then it's rotated to the local time zone for display. If
> you can post a counterexample then I'd be happy to stand corrected.
Isn't the following what you want?
************************************************************
»Script« wurde gestartet: Sun Apr 24 19:05:06 2005
ncq@hermes:~> psql -d gnumed -U any-doc
Welcome to psql, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit
gnumed=> select version();
version
---------------------------------------------------------------
PostgreSQL 7.1.3 on i686-pc-linux-gnu, compiled by GCC 2.95.3
(1 row)
gnumed=> create teable test (f timestamp with time zone);
CREATE
gnumed=> set time zone '+2:00';
SET VARIABLE
gnumed=> show time zone;
NOTICE: Time zone is +2:00
SHOW VARIABLE
gnumed=> insert into test (f) values (now());
INSERT 6462134 1
gnumed=> SELECT * from test;
f
------------------------
2005-04-24 17:06:10+00
(1 row)
gnumed=> select f, f::timestamp at time zone 'MEZ' from test;
f | timezone
------------------------+------------------------
2005-04-24 17:06:10+00 | 2005-04-24 18:06:10+01
(1 row)
gnumed=> \q
ncq@hermes:~> exit
exit
»Script« beendet: Sun Apr 24 19:06:59 2005
************************************************************
Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
