Thread: 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