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
Date: Thu, 29 Jun 2023 13:52:42 -0400
Message-ID: <1573129.1688061162@sss.pgh.pa.us> (raw)
In-Reply-To: <18007-036782307e002bf6@postgresql.org>
References: <18007-036782307e002bf6@postgresql.org>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, braiamp+pg@gmail.com, 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: <1573129.1688061162@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox