Re: problem creating rtree index on timestamptz

Hussein Patni <security(at)cosbit(dot)com> writes:
> It seems subraction of an interval from a
> timestamptz is not immutable. Should this be the case?

Yes, because the results depend on your local timezone. For example:

regression=# set TimeZone TO 'EST5EDT';
SET
regression=# select '2005-02-28 17:00-05'::timestamptz - '6 months'::interval;
?column?
------------------------
2004-08-28 17:00:00-04
(1 row)

regression=# set TimeZone TO 'GMT';
SET
regression=# select '2005-02-28 17:00-05'::timestamptz - '6 months'::interval;
?column?
------------------------
2004-08-28 22:00:00+00
(1 row)

regression=# select '2004-08-28 17:00:00-04'::timestamptz - '2004-08-28 22:00:00+00';
?column?
-----------
-01:00:00
(1 row)

regression=#

It's probably true that subtracting an interval expressed in seconds (or
equivalent units) is an immutable operation, but we don't have a way to
capture that statement in data types, since there's no such restricted
interval datatype.

You might consider making a function defined like timestamptz - integer
(or float) in which the integer is considered as a number of seconds.
You could safely mark that as immutable, I think.

regards, tom lane