Hi Rajesh:
On Fri, 8 Jul 2022 at 12:36, Rajesh S <rajesh.s@fincuro.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
Postgresthe same query returns result as "19 days". Because of this we are getting errors while assigning this query
outputto a numeric variable saying "ERROR: invalid input syntax for type numeric: "1825 days"" and "ERROR: operator
doesnot exist: interval + integer". To avoid changing the application code in many places to extract the number of
daysalone, 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.