Re: - operator overloading not giving expected result

Hi Rajesh:

On Fri, 8 Jul 2022 at 12:36, Rajesh S <rajesh(dot)s(at)fincuro(dot)com> wrote:
> We are migrating our database from Oracle to Postgresql. In oracle we have used this syntax "SELECT ('1999-12-30'::DATE) - ('1999-12-11'::DATE)" to get difference between two dates as a integer output (ex: 19). But in Postgres the same query returns result as "19 days". Because of this we are getting errors while assigning this query output to a numeric variable saying "ERROR: invalid input syntax for type numeric: "1825 days"" and "ERROR: operator does not exist: interval + integer". To avoid changing the application code in many places to extract the number of days alone, we tried operator overloading concept as below.

Are you posting exact code? Because current doc states:

date - date → integer
Subtract dates, producing the number of days elapsed
date '2001-10-01' - date '2001-09-28' → 3

I remember it always working that way and 8.0 docs document it that
way too ( and I've used a lot of date arithmetic even in 7.x )

Maybe your EXACT code is not what you have pasted, but rather you
inserting dates in timestamp columns and then substracting said
columns? Because even your operations are defined in terms of
timestamptz, not dates.

Francisco Olarte.