SET TIME ZONE with GMT+X notation

[Jason Priebe]

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
ja**********@yahoo.com

[Tom Lane]

ja**********@yahoo.com (Jason Priebe) writes:

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

Uh, have you read
http://www.postgresql.org/docs/view....TETIME-CURRENT

particularly the point about

It is important to realize that CURRENT_TIMESTAMP and related functions
return the start time of the current transaction; their values do not
change during the transaction. timeofday() returns the wall clock time
and does advance during transactions.
regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[jason_priebe]

--- Tom Lane <tg*@sss.pgh.pa.us> wrote:

ja**********@yahoo.com (Jason Priebe) writes:

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

Uh, have you read

http://www.postgresql.org/docs/view....TETIME-CURRENT
particularly the point about

It is important to realize that CURRENT_TIMESTAMP
and related functions
return the start time of the current transaction;
their values do not
change during the transaction. timeofday() returns
the wall clock time
and does advance during transactions.

Yep. I understand that. We're having some issues
with our application where gradually the values from
CURRENT_TIMESTAMP fall further and further behind.
As far as we know, we don't have any transactions
open. We are, however, using PHP's pg_pconnect()
function to connect to the database. The only thing
I can think of is that perhaps a transaction is left
hanging (perhaps due to abnormal termination of a
process), and then another process picks up that
pooled connection and "inherits" the open transaction?
It doesn't make a lot of sense, but it's the only
thing I can think of.

-Jason Priebe
__________________________________
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org