On Fri, 15 Dec 2000, Alessio Bragadini wrote:
> Sorry, I am trying to find my way in formatting timestamps for different
> timezones and I am a little confused.
>
> [ PostgreSQL 7.0.0 on alphaev6-dec-osf4.0f, compiled by cc ]
>
> Let's imagine
> CREATE TABLE tztest (id SERIAL, v TEXT, ts TIMESTAMP DEFAULT now());
>
> How can I format a
> SELECT to_char(ts,'DD/MM/YYYY HH:MI:SS')
> in order to have the accompanying timezone for the timestamp?
> If I select the ISO format, I ofcourse have it ('2000-12-15
> 13:09:59+02')
> but I cannot find a to_char element for it, either in offset or codes
> (which I'd prefer).
> Is this possible?
Yes it's possible, but in freezed 7.1 *only*. It's 'TZ' and output is
abbreviation of timezone, +02 (digit version) is not supported.
test=# SELECT to_char(now(), 'DD/MM/YYYY HH:MI:SS TZ'); to_char
-------------------------15/12/2000 01:29:14 CET
(1 row)
> village=# select ts from tztest;
> ts
> ------------------------
> 2000-12-15 13:09:59+02
> (1 row)
>
> village=# set TimeZone TO PST;
> SET VARIABLE
> village=# select ts from tztest;
> ts
> ------------------------
> 2000-12-15 13:09:59+02
> (1
> row)
>
> or maybe I just don't understand the whole picture...
You must use same names (definitions) as are used in your OS
(an example on Linux at /usr/share/zoneinfo)
test=# set TimeZone TO 'Japan';
SET VARIABLE
test=# select now(); now
------------------------2000-12-15 21:40:52+09
(1 row)
test=# set TimeZone TO 'EST';
SET VARIABLE
test=# select now(); now
------------------------2000-12-15 07:41:18-05
(1 row)
test=# set TimeZone TO 'GMT';
SET VARIABLE
test=# select now(); now
------------------------2000-12-15 12:41:29+00
(1 row)
Karel