From: Tom Lane <tgl@sss.pgh.pa.us>
To: braiamp+pg@gmail.com
cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18007: age(timestamp, timestamp) is marked as immutable,
 but using age(date, date) says it's not
In-reply-to: <18007-036782307e002bf6@postgresql.org>
References: <18007-036782307e002bf6@postgresql.org>
Comments: In-reply-to PG Bug reporting form <noreply@postgresql.org>
	message dated "Thu, 29 Jun 2023 17:08:25 -0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <1573128.1688061162.1@sss.pgh.pa.us>
Date: Thu, 29 Jun 2023 13:52:42 -0400
Message-ID: <1573129.1688061162@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/1573129.1688061162%40sss.pgh.pa.us>
Precedence: bulk

PG Bug reporting form <noreply@postgresql.org> writes:
> There's no much difference between timestamp and dateT00:00:00.000, yet
> using age(date, date) for some reason internally doesn't type coerce
> correctly into the appropriated types.

There is no age(date, date) function.  What we have is age(timestamp,
timestamp) and age(timestamptz, timestamptz), so the parser has to
choose which type to coerce to --- and it prefers timestamptz.
Perhaps this is surprising as an isolated fact, but I believe what
it stems from is that timestamptz is the "preferred" type in this
type category.  That's not something that's likely to change.
Then what you have within the expression is a coercion from date
to timestamptz, which depends on the time zone, so it's not
immutable.

Bottom line is that you'd better cast the dates to timestamp
explicitly.  Or you could make an age(date, date) wrapper
function that does that.

> I remember that on a previous
> versions (not sure if it was 14) this wasn't the case,

Doubt it.  Nothing here has changed in a couple of decades.
Maybe you had a wrapper function that you forgot to bring over?

			regards, tom lane