SET TIME ZONE with GMT+X notation - Mailing list pgsql-general

I'm a bit confused about SET TIME ZONE and its effect on PostgreSQL's
date processing.

In my experience with timestamps in all other *nix-based software
systems, a timestamp is always a numeric representation of the
time elapsed since the epoch, in GMT.  Thus, a function that
returns the current timestamp should always return the same value,
regardless of timezone.  The display of that value may change based
on the system's timezone, but the value stored does not vary based
on current timezone settings.

For the most part, I've seen the same from PostgreSQL.  But I'm
seeing some strange behavior when I use the "GMT+X" format for
timezone specifications.

Here's a simple table:

foo=> \d bar
                                    Table "bar"
   Column   |           Type           |                 Modifiers
------------+--------------------------+-------------------------------------------
 timestamp1 | timestamp with time zone | not null default timeofday()
 timestamp2 | timestamp with time zone | not null default
"timestamp"('now'::text)
 media_type | character varying(50)    | not null default 'IMAGE'


Note that it uses timeofday() for the default for one timestamp and
"now" for the default for the other (we've been experimenting with the
differences between the two, as we've seen some serious drift in the
values returned by "now" -- but that's another story).

So we insert a record, set the time zone to "GMT+4" (which corresponds
to the current offset for EDT), then insert another record:

foo=> insert into bar (media_type) values ('baz'); set time zone
'GMT+4'; insert into bar (media_type) values ('baz');
INSERT 469438 1
SET VARIABLE
INSERT 469439 1

Now look at the time values inserted:

foo=> select date_part('epoch',timestamp1),
date_part('epoch',timestamp2) from bar;
    date_part     |    date_part
------------------+------------------
 1060783749.77958 | 1060783749.77807
 1060769349.78216 | 1060783749.78164

Note that in the first column (the one that uses timeofday() for
its default values), there is a four-hour difference between
the values, even though the inserts were performed about 3ms
apart! The column that uses 'now' for its default values does not
exhibit this difference.

Now repeat the experiment using "America/New_York" instead of
'GMT+4', and the effect goes away:

foo=> insert into bar (media_type) values ('baz'); set time zone
'America/New_York'; insert into bar (media_type) values ('baz');
INSERT 469442 1
SET VARIABLE
INSERT 469443 1
foo=> select date_part('epoch',timestamp1),
date_part('epoch',timestamp2) from bar;
    date_part     |    date_part
------------------+------------------
 1060783843.09787 |  1060783843.0957
 1060783843.10056 | 1060783843.09996
(2 rows)


I apologize for the long post.  But I didn't see a clearer way to
communicate this problem.  I'm seeing this with PostgreSQL 7.2.3
on RH Linux 7.3.  I know it's not the most current version, but
I've checked the HISTORY files to make sure there hasn't been a
fix to this problem.  I saw a few timezone changes, but I don't
think this problem was addressed.

The reason this is a fairly large problem for me is that I need to
be able to use the 'GMT+X' notatation using PostgreSQL under Cygwin.
It seems that this is the only notation accepted by the cygwin port
of PostgreSQL.

Thanks for any insight.

Jason Priebe
jason_priebe@yahoo.com