Re: Interval+tz-tz gives unexpected result

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Henshall, Stuart - Design & Print" <SHenshall(at)westcountry-design-print(dot)co(dot)uk>
Cc: "'pgsql-bugs(at)postgresql(dot)org'" <pgsql-bugs(at)postgresql(dot)org>
Subject: Re: Interval+tz-tz gives unexpected result
Date: 2002-11-11 17:09:52
Message-ID: 2793.1037034592@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

"Henshall, Stuart - Design & Print" <SHenshall(at)westcountry-design-print(dot)co(dot)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

In response to

Browse pgsql-bugs by date

  From Date Subject
Next Message Robert John Shepherd 2002-11-11 22:28:04 Bug in pg_dump
Previous Message Henshall, Stuart - Design & Print 2002-11-11 14:26:34 Interval+tz-tz gives unexpected result