Andrew - Supernews <andrew+nonews@supernews.com> writes:
> Um, what? Under what conditions is it permissable for simple arithmetic on
> (only) timestamptz values (which may have originated in different timezones
> neither of which is the current one) to be dependent on the current timezone
> setting?

Timestamp subtraction will give different answers depending on whether
there's a DST adjustment in between.

regression=# select '2005-10-31'::timestamptz - '2005-10-28'::timestamptz;
?column?
-----------------
3 days 01:00:00
(1 row)

regression=# set timezone = 'Japan';
SET
regression=# select '2005-10-31'::timestamptz - '2005-10-28'::timestamptz;
?column?
----------
3 days
(1 row)

BTW, if we were doing subtraction symbolically as I think we should,
these *would* give the same answer, ie, '3 days' in both cases. Care to
rethink your opposition to that idea?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

On 2005-10-26, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Andrew - Supernews <andrew+nonews@supernews.com> writes:
>> Um, what? Under what conditions is it permissable for simple arithmetic on
>> (only) timestamptz values (which may have originated in different timezones
>> neither of which is the current one) to be dependent on the current timezone
>> setting?
>
> Timestamp subtraction will give different answers depending on whether
> there's a DST adjustment in between.

no, it _WILL NOT_.

In your example, the result is different between timezones because the
_input data_ is different. '2005-10-31'::timestamptz designates a different
time in US/Eastern than it does in Japan, or UTC, or whatever.

Or to put it in terms of the code: in 8.0, timestamptz_in is stable
rather than immutable (since it depends on timezone), while timestamptz_mi
is immutable (result depends only on the input values).

In 8.0, I'm guaranteed that for timestamptz values, a+(b-a) = b in all
cases regardless of timezone. 8.1 has broken that.

> BTW, if we were doing subtraction symbolically as I think we should,
> these *would* give the same answer, ie, '3 days' in both cases. Care to
> rethink your opposition to that idea?

No. If you want symbolic subtraction, that's what age() is for. If you
break the subtraction operator, you leave no means of doing _accurate_
subtraction.

--
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services

Andrew - Supernews wrote:
> On 2005-10-26, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > Andrew - Supernews <andrew+nonews@supernews.com> writes:
> >> Um, what? Under what conditions is it permissable for simple arithmetic on
> >> (only) timestamptz values (which may have originated in different timezones
> >> neither of which is the current one) to be dependent on the current timezone
> >> setting?
> >
> > Timestamp subtraction will give different answers depending on whether
> > there's a DST adjustment in between.
>
> no, it _WILL NOT_.
>
> In your example, the result is different between timezones because the
> _input data_ is different. '2005-10-31'::timestamptz designates a different
> time in US/Eastern than it does in Japan, or UTC, or whatever.
>
> Or to put it in terms of the code: in 8.0, timestamptz_in is stable
> rather than immutable (since it depends on timezone), while timestamptz_mi
> is immutable (result depends only on the input values).
>
> In 8.0, I'm guaranteed that for timestamptz values, a+(b-a) = b in all
> cases regardless of timezone. 8.1 has broken that.

Yes, that is a valid problem, but what solution do we have at this
stage?

--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster