Re: Confused by timezones

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