Thread: Confused by timezones

Confused by timezones

From
Alessio Bragadini
Date:
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?

On a more general ground, I checked the 'Date/Time Data Types' section
of the user manual, but I don't manage to have the expected behaviour,
with either the PGTZ env variable or the SET TIMEZONE command. Here's an
example (my default is EET i.e. +02):

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...

P.S. Ofcourse I can use external functions, e.g. Date::Manip since I
code in Perl, but I'd prefer to leave this task to the database itself.

-- 
Alessio F. Bragadini        alessio@albourne.com
APL Financial Services        http://village.albourne.com
Nicosia, Cyprus             phone: +357-2-755750

"It is more complicated than you think"    -- The Eighth Networking Truth from RFC 1925


Re: Confused by timezones

From
Karel Zak
Date:
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 



Re: Confused by timezones

From
Alessio Bragadini
Date:
Karel Zak wrote:

>  Yes it's possible, but in freezed 7.1 *only*. It's 'TZ' and output is

Thanks, on my experimental 7.1 works perfectly, another reason to switch
as soon as possible. :-)

>  You must use same names (definitions) as are used in your OS
> (an example on Linux at /usr/share/zoneinfo)

In 7.1 works. Is it supposed to work also in 7.0? Because then it would
be a configuration problem on my main system.

Since I have a website with registered users, with associated timezone,
I would like to show all timestamps based on the user's timezone, and
the best way would be to set a session configuration. My only fear is
that Apache::DBI (which reuses the same connection for different pages)
could intermix such information. Any comment on this would be
appreciated.

Thanks

-- 
Alessio F. Bragadini        alessio@albourne.com
APL Financial Services        http://village.albourne.com
Nicosia, Cyprus             phone: +357-2-755750

"It is more complicated than you think"    -- The Eighth Networking Truth from RFC 1925


Re: Confused by timezones

From
Tom Lane
Date:
Alessio Bragadini <alessio@albourne.com> writes:
> village=# set TimeZone TO PST;

I'm guessing that's not a legal timezone name on your platform.
On my box I have to spell it "PST8PDT" ... note that the displayed
abbreviation is not the same as the name used to set the timezone.
        regards, tom lane


Re: Confused by timezones

From
Tom Lane
Date:
Alessio Bragadini <alessio@albourne.com> writes:
>> You must use same names (definitions) as are used in your OS
>> (an example on Linux at /usr/share/zoneinfo)

> In 7.1 works. Is it supposed to work also in 7.0?

Yes; as far as I know this hasn't changed...
        regards, tom lane


Re: Confused by timezones

From
Alessio Bragadini
Date:
Tom Lane wrote:

> I'm guessing that's not a legal timezone name on your platform.
> On my box I have to spell it "PST8PDT" ... note that the displayed
> abbreviation is not the same as the name used to set the timezone.

I used PST as an example, but it doesn't work with any other zone,
including GMT (our localtime is EET). I suspect it's a configuration
problem on our Digital machine (with Digital Unix 4.0F): zoneinfos are
under /etc/zoneinfo. 

Do the functions use those files or it's just an OS call? Is configure
involved at all in this area?

Thanks again

-- 
Alessio F. Bragadini        alessio@albourne.com
APL Financial Services        http://village.albourne.com
Nicosia, Cyprus             phone: +357-2-755750

"It is more complicated than you think"    -- The Eighth Networking Truth from RFC 1925