Обсуждение: Confused by timezones
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
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
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
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
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
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