Hi,
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.
CREATE OR REPLACE FUNCTION public.dt_minus_dt(
dt1 timestamp without time zone,
dt2 timestamp without time zone)
RETURNS integer
LANGUAGE 'edbspl'
COST 100
VOLATILE SECURITY DEFINER PARALLEL UNSAFE
AS $BODY$
days INTEGER;
BEGIN
SELECT DATE_PART('day', dt1::timestamp - dt2::timestamp)::integer
INTO days;
RETURN days;
END
$BODY$;
CREATE OPERATOR public.- (
FUNCTION = public.dt_minus_dt,
LEFTARG = timestamp without time zone,
RIGHTARG = timestamp without time zone
);
When we execute "SELECT ('1999-12-30'::DATE) - ('1999-12-11'::DATE)", we
are still getting "19 days" as result and not "19" as we expect. The
above same function works as expected for the operator + or ===.
CREATE OPERATOR public.+ (
FUNCTION = public.dt_minus_dt,
LEFTARG = timestamp without time zone,
RIGHTARG = timestamp without time zone
);
SELECT ('1999-12-30'::DATE) + ('1999-12-11'::DATE)
CREATE OPERATOR public.=== (
FUNCTION = public.dt_minus_dt,
LEFTARG = timestamp without time zone,
RIGHTARG = timestamp without time zone
);
SELECT ('1999-12-30'::DATE) === ('1999-12-11'::DATE)
I really appreciate anyone's help in resolving this case. Thanks in
advance.
Rajesh S
