Re: BUG #18007: age(timestamp, timestamp) is marked as immutable, but using age(date, date) says it's not

On Thu, Jun 29, 2023 at 1:52 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> PG Bug reporting form <noreply(at)postgresql(dot)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.

According to \df+ age both timestamptz and timestamp are immutable:

-[ RECORD 2 ]-------+--------------------------------------------------------------------
Schema | pg_catalog
Name | age
Result data type | interval
Argument data types | timestamp without time zone, timestamp without time zone
Type | func
Volatility | immutable
Parallel | safe
Owner | postgres
Security | invoker
Access privileges |
Language | internal
Source code | timestamp_age
Description | date difference preserving months and years
-[ RECORD 4 ]-------+--------------------------------------------------------------------
Schema | pg_catalog
Name | age
Result data type | interval
Argument data types | timestamp with time zone, timestamp with time zone
Type | func
Volatility | immutable
Parallel | safe
Owner | postgres
Security | invoker
Access privileges |
Language | internal
Source code | timestamptz_age
Description | date difference preserving months and years

So, whatever type is coerced into pre-function evaluation comes
with strange results. I'm not aware of a way that I can see what
kind of type is being coerced into.

> 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

--
Braiam