pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedConfused by timezones
6+ messages / 3 participants
[nested] [flat]
* Confused by timezones
@ 2000-12-15 12:23 Alessio Bragadini <alessio@albourne.com>
0 siblings, 2 replies; 6+ messages in thread
From: Alessio Bragadini @ 2000-12-15 12:23 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Confused by timezones
@ 2000-12-15 12:45 Karel Zak <zakkr@zf.jcu.cz>
parent: Alessio Bragadini <alessio@albourne.com>
1 sibling, 1 reply; 6+ messages in thread
From: Karel Zak @ 2000-12-15 12:45 UTC (permalink / raw)
To: Alessio Bragadini <alessio@albourne.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Confused by timezones
@ 2000-12-15 15:23 Alessio Bragadini <alessio@albourne.com>
parent: Karel Zak <zakkr@zf.jcu.cz>
0 siblings, 1 reply; 6+ messages in thread
From: Alessio Bragadini @ 2000-12-15 15:23 UTC (permalink / raw)
To: Karel Zak <zakkr@zf.jcu.cz>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Confused by timezones
@ 2000-12-15 16:04 Tom Lane <tgl@sss.pgh.pa.us>
parent: Alessio Bragadini <alessio@albourne.com>
1 sibling, 1 reply; 6+ messages in thread
From: Tom Lane @ 2000-12-15 16:04 UTC (permalink / raw)
To: Alessio Bragadini <alessio@albourne.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Confused by timezones
@ 2000-12-15 16:08 Tom Lane <tgl@sss.pgh.pa.us>
parent: Alessio Bragadini <alessio@albourne.com>
0 siblings, 0 replies; 6+ messages in thread
From: Tom Lane @ 2000-12-15 16:08 UTC (permalink / raw)
To: Alessio Bragadini <alessio@albourne.com>; +Cc: Karel Zak <zakkr@zf.jcu.cz>; pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Confused by timezones
@ 2000-12-18 15:16 Alessio Bragadini <alessio@albourne.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 6+ messages in thread
From: Alessio Bragadini @ 2000-12-18 15:16 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql; Arrigo Triulzi <arrigo@albourne.com>; Adriaan Joubert <a.joubert@albourne.com>
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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2000-12-18 15:16 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-12-15 12:23 Confused by timezones Alessio Bragadini <alessio@albourne.com>
2000-12-15 12:45 ` Karel Zak <zakkr@zf.jcu.cz>
2000-12-15 15:23 ` Alessio Bragadini <alessio@albourne.com>
2000-12-15 16:08 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-12-15 16:04 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-12-18 15:16 ` Alessio Bragadini <alessio@albourne.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox