Re: Inconsistant DOW...

Richard Huxton <dev(at)archonet(dot)com> writes:
> W.B.Hill wrote:
>> SELECT d+'45 days ago'::interval FROM test;
>>
>> Why the different times??? Why the times???

> At a guess, the date is being converted into a timestamp with timezone
> so you can add the interval to it.

Yeah, I think that will be the preferred conversion (and the fact that
the output shows a timezone offset is a dead giveaway ;-))

However there is also a timestamp-without-timezone plus interval
operator, so one good solution is to explicitly cast the date to
timestamp without tz and then add the interval.

Another and probably even better solution for this problem is to forget
timestamps and intervals, and use the date plus/minus integer operators
(ie, write "d - 45"). If you don't care about sub-day resolution there
is no reason to get into timestamps at all.

BTW, since 7.3 there has been a date-plus-interval operator yielding
timestamp without time zone, which I believe Lockhart added specifically
to avoid the unwanted promotion to timestamptz in this scenario.
So the third answer is to update to something less ancient than PG 7.2.

regards, tom lane