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@sss.pgh.pa.us> wrote:
>
> 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.

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