Thread: Interval+tz-tz gives unexpected result

dev=# select '0 sec'::interval+'2002/10/21 09:48'::timestamptz-'2002/10/21
09:30'::timestamptz;
 ?column?
----------
 -09:30
(1 row)

I would have expected '00:18'

dev=# select version();
                           version
--------------------------------------------------------------
 PostgreSQL 7.2.3 on i686-pc-cygwin, compiled by GCC 2.95.3-5
(1 row)

Thank you,
- Stuart

"Henshall, Stuart - Design & Print" <SHenshall@westcountry-design-print.co.uk> writes:
> dev=# select '0 sec'::interval+'2002/10/21 09:48'::timestamptz-'2002/10/21
> 09:30'::timestamptz;
>  ?column?
> ----------
>  -09:30
> (1 row)

In 7.3 I get

regression=# select '0 sec'::interval+'2002/10/21 09:48'::timestamptz-'2002/10/21 09:30'::timestamptz;
ERROR:  Unable to identify an operator '+' for types 'interval' and 'timestamp with time zone'
        You will have to retype this query using an explicit cast

I'm not sure what interpretation 7.2 is putting on this, but I see that
the addition is yielding a timestamp without time zone:

regression=# select '0 sec'::interval+'2002/10/21 09:48'::timestamptz;
      ?column?
---------------------
 2002-10-21 00:00:00
(1 row)

I *think* it may be coercing the inputs so it can use the date + time
without timezone operator.  Which is a tad silly.  We've tightened up
the implicit coercions for 7.3, which is why it doesn't do this anymore.

In the meantime, the real issue for you is that there's a timestamptz +
interval operator, but not interval + timestamptz, so you need to recast
the command as timestamptz + interval - timestamptz.

            regards, tom lane